RE: large matview log, does it lock base table when dropping?
- From: Yavor Ivanov <Yavor_Ivanov@xxxxxxxx>
- To: "dannorris@xxxxxxxxxxxxx" <dannorris@xxxxxxxxxxxxx>
- Date: Fri, 1 Aug 2008 09:43:35 +0300
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
-- //www.freelists.org/webpage/oracle-l
Other related posts:
