Re: Materialized View Log - do they shrink?

From: M Rothwell <ThisIsABadAddress_at_toobad.com>
Date: Fri, 10 Sep 2004 13:56:02 -0700
Message-ID: <41421531$1@usenet01.boi.hp.com>

Pratap wrote:

> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
>

>>If, for some reason, Oracle thinks that the materialized view log
>>is also there to support a second materialized view, it will not
>>delete the log entries - so the log table will grow.  This can happen
>>quite easily (or used to in earlier versions) when things went a bit
>>wrong and DBAs tried to clean up the mess manually - sometimes
>>a materialized view would be dropped, but remain registered at
>>the master database - which would then allow the log to grow
>>indefinitely because the "ghost" MV never asked to be refreshed.

>
>
> I observed in 9i that it does purge the mv log when any one mv is
> refreshed. I had to refresh all the MVs in one go using -
> dbms_mview.refresh ( 'mv1, mv2' )
>
> Regards,
>
> Pratap

I ran some tests last night on my configuration. All three MVlogs had been purged after the MView had been refreshed.

Thanks for all the comments.

Michael Received on Fri Sep 10 2004 - 15:56:02 CDT