1. Home
  2. Knowledge Base
  3. Database Administration
  4. 10g materialized view complete refresh using atomic_refresh

10g materialized view complete refresh using atomic_refresh

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.

The following two tabs change content below.

Arjun Raja

Latest posts by Arjun Raja (see all)

Updated on June 2, 2021

Was this article helpful?

Related Articles

Leave a Comment