#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    2
    Rep Power
    0

    Problem refreshing Materialized View in Oracle SQL Developer


    I have a database schema with a Materialized View (MV) that updates a products table shown on a website. This schema was recently imported to an 11g database from 8i, but since this import I have been unable to refresh my schema’s Materialized View.

    The MV_PRODUCT_MASTER Materialized View attaches price data to product data that is stored in a normal WEB_PRODUCT table, and create an MV table as a result. The output MV table is what the website pulls data from to display to the site users. Since it is an MV table, this table cannot be edited directly. In order to change, say, a product description, I need to alter the description field in WEB_PRODUCT and manually refresh the MV_PRODUCT_MASTER table. Only through the refresh will the upstream edits appear in the MV_PRODUCT_MASTER table and be visible on the website.

    In my old 7.6.0.11 copy of TOAD, I could manually refresh these MVs easily, by opening the “Snapshots” tab, right clicking on the MV I wanted to refresh and selecting the “Refresh” option. Since the schema was imported to 11g, I have been using the Oracle SQLDeveloper tool to manage the schema. SQLDeveloper doesn’t have a clear method for manually refreshing an MV, or else the method I am using isn’t working.

    If I right click on the MV_PRODUCT_MASTER Materialized View object, and choose “Other Actions”, I see the following choices:

    Shrink Materialized View
    Compile Materialized View
    Force Materialized View Refresh
    Rebuild Materialized View

    …I assumed that “Force Materialized View Refresh” was the right choice, and chose that. This option displays the SQL:

    alter materialized view "WEBADMIN"."MV_PRODUCT_MASTER" consider fresh

    When I apply this, I get the message: “Materialized view “MV_PRODUCT_MASTER” has been set torefreshed”. However, no changes appear in the MV output table. i.e. if I make a specific change to a row in the WEB_PRODUCT table, the change is not being carried into the MV_PRODUCT_MASTER table, so that indicated that the refresh is not actually happening. The MV table appears to believe it is being refreshed:

    REWRITE_CAPABILITY GENERAL
    REFRESH_MODE DEMAND
    REFRESH_METHOD COMPLETE
    BUILD_MODE IMMEDIATE
    FAST_REFRESHABLE NO
    LAST_REFRESH_TYPE COMPLETE
    LAST_REFRESH_DATE 06-APR-10
    STALENESS UNKNOWN

    …but it isn’t showing any changes.

    What am I doing wrong? Is there a plain SQL statement I can run in order to run these refreshes, instead of using the SQLDeveloper GUI? Thanks for any advice on this.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    348
    Originally Posted by Nostromo_
    Is there a plain SQL statement I can run in order to run these refreshes, instead of using the SQLDeveloper GUI? Thanks for any advice on this.
    You can use DBMS_MVIEW.REFRESH() to force a refresh of a materialized view.

    Details are in the manual:

    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_mview.htm#i997194
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    2
    Rep Power
    0
    Originally Posted by shammat
    You can use DBMS_MVIEW.REFRESH() to force a refresh of a materialized view.
    Thanks very much for your reply, shammat. I tried executing the following statements:

    DBMS_MVIEW.REFRESH('WEBADMIN.MV_PRODUCT_MASTER','c'); (i.e. including the schema name)

    and

    DBMS_MVIEW.REFRESH('MV_PRODUCT_MASTER','c');

    ...both statements returned "ORA-00900: invalid SQL statement" errors. Am I using bad syntax, or could the DBMS package not be installed on the database server?

    Thanks again.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    348
    Originally Posted by Nostromo_
    ...both statements returned "ORA-00900: invalid SQL statement" errors. Am I using bad syntax, or could the DBMS package not be installed on the database server?
    I think you need to use

    Code:
    EXECUTE DBMS_MVIEW.REFRESH('MV_PRODUCT_MASTER','c');
    But I don't use SQL Developer so I'm not 100% sure, you might need to consult SQL Developer's manual to find out how to run stored procedures
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    1
    Rep Power
    0
    Originally Posted by shammat
    I think you need to use

    Code:
    EXECUTE DBMS_MVIEW.REFRESH('MV_PRODUCT_MASTER','c');
    But I don't use SQL Developer so I'm not 100% sure, you might need to consult SQL Developer's manual to find out how to run stored procedures
    Hi
    I am trying to use that with Oracle developer still I get the same error . Do you have any idea about this?

    Regards,
    Romit

IMN logo majestic logo threadwatch logo seochat tools logo