- 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.
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
This guarantees that Oracle will first TRUNCATE the MIVEW and then refresh it thereby excluding the need of the use of the UNDO tablespace.