RE: large matview log, does it lock base table when dropping?

                The mvew log keeps all the data needed for refreshing the oldest non-refreshed mvew, based on the given table. I had one case years ago (on 9.1) when a master with 12 mvew sites looses one site. The site had was lost forever, which means it's mvews will never refresh. However, the master cannot know that and all mvew logs started to grow. There were about  1500 tables replicated form the master to each of the mvew sites, and all the logs would never shrink. Recreating the mvew logs was not an option, because we would have to make complete refresh to all other 11 sites.
                Since then I know, that if there is some mvew that will never refresh, the solution is to use DBMS_MVIEW.UNREGISTER_MVIEW on the master.

                There is another case. If you have 1 GB table changed frequently, and 1 GB mvew not refreshed for a long time, it may be faster to rebuild the whole mvew (1GB) rather than applying 100 GB changes. In that case you make a complete refresh:
DBMS_MVIEW.REFRESH(...,'c');
                This will rebuild the mvew from scratch and, if there are no other mvews on that log, it will empty the log from the data.

Regards,
Yavor Ivanov
Oracle Certified Master

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dan Norris Sent: Thursday, July 31, 2008 9:20 PM
To: Thomas Day
Cc: Oracle L
Subject: Re: large matview log, does it lock base table when dropping?

There's actually 100G of data in it (the table changes a lot and this has been capturing a month's worth of data), so I'm not sure there's anything that can be shrunk at this point.

Thanks for the tip, though, I think that may come in handy for some other logs at some point.

Dan

Thomas Day wrote:
Have you tried

alter materialized view log on master enable row movement;

alter materialized view log on master shrink size;

This is supposed to be very DML friendly. I haven't tested it for locks but it's supposed to only have row locks on data in the log. It could take a while for 100G though.
-- http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 01 2008 - 01:43:35 CDT