News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

10g materialized view complete refresh using atomic_refresh

  • Posted by Arjun Raja
  • On July 8, 2009
  • 0 Comments

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.

 

0 Comments

Leave Reply

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