Oracle DBA Tips and Techniques

By Arjun Raja

10g materialized view complete refresh using atomic_refresh

Posted by Arjun Raja on July 8, 2009

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: