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.

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

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.

I think you need to use

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