Oracle DBA Tips and Techniques

By Arjun Raja

MATERIALIZED VIEWS REFRESH – FORCE TRUNCATE AND INSERT .

Posted by Arjun Raja on November 3, 2011

I faced this issue when we upgraded 9i to 10g – you may face the same issue if you upgrade from 9i to 11g as well.

This is the story :

After migrating from 9i to 10g or 11g, 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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: