SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Refresh Materialized View after truncating the base table

user518034Dec 13 2006 — edited Dec 14 2006

We are at 10.2.0.1, Windows 2003 Server.

I am trying to refresh a Materialized View after truncating the base table. After the base table is truncated and reloaded, if I select STALENESS from SYS.USER_MVIEWS I get NEEDS_COMPILE. I cannot refresh the Materialized View and get it to show as FRESH. If I call DBMS_MVIEW.REFRESH or DBMS_MVIEW.REFRESH_ALL_MVIEWS it shows as STALE. I have tried ALTER MATERIALIZED VIEW <view name> COMPILE... same thing, it is in USER_MVIEWS as STALE.

The only was I can get this to work is by dropping and recreating the Materialized View. that works, but it seems kind of extreme. Is there some magic that you need to perform to refresh after a DML operation like a Truncate?

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 11 2007

Added on Dec 13 2006

3 comments

485 views