Link to home
Start Free TrialLog in
Avatar of Nostromo77
Nostromo77

asked on

Problem refreshing a Materialized View in 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.
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think that there is some error in your MV, because you see
STALENESS      UNKNOWN
After successfully refreshed it ought to be FRESH.
Here you can read about it:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1105.htm#i1582466
Avatar of Nostromo77
Nostromo77

ASKER

Thanks for the advice. It turned out to be a GUI problem of some sort: in the free Oracle SQL Developer, the statement

exec DBMS_MVIEW.REFRESH('WEBADMIN.MV_MY_MATERIALIZED_VIEW','c');

...would not run, but in the Free version of TOAD, this statement ran just fine. Odd...

Thank you again for the help.