Loading....

After migrating from 9i to 10g, take care in case you have materialized views and are doing a complete refresh.

The following command to refresh an MVIEW resulted in the database undo tablespace filling up and the appearance of the dreaded snapshot too old error.

execute DBMS_MVIEW.REFRESH(‘OBJECT_OWNER.TEST_MVIEW,’C’);

That is because in 10g the behaviour of Oracle in case the atomic_refresh=false parameter is not included is to delete the data first before inserting or refreshing the view.

To avoid this, issue the following command

execute DBMS_MVIEW.REFRESH(‘OBJECT_OWNER.TEST_MVIEW’,’C’,ATOMIC_REFRESH=>false);

This guarantees that Oracle will first TRUNCATE the MIVEW and then refresh it thereby excluding the need of the use of the UNDO tablespace.

Please follow and like us:

Last Update: July 8, 2009  

July 8, 2009 192 Arjun Raja
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, , ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?