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.
Nostromo77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lwadwellCommented:
try ...
    exec DBMS_MVIEW.REFRESH('WEBADMIN.MV_PRODUCT_MASTER', 'C');
or
    exec DBMS_SNAPSHOT.REFRESH(
        LIST                 => 'WEBADMIN.MV_PRODUCT_MASTER'
       ,PUSH_DEFERRED_RPC    => TRUE
       ,REFRESH_AFTER_ERRORS => FALSE
       ,PURGE_OPTION         => 1
       ,PARALLELISM          => 0
       ,ATOMIC_REFRESH       => TRUE);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Helena Markováprogrammer-analystCommented:
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
0
Nostromo77Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.