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.
Arjun Raja
