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