Re: reorganize snapshot log

From: Anurag Varma <avoracle_at_gmail.com>
Date: 13 Mar 2007 07:16:32 -0700
Message-ID: <1173795392.276308.237670@30g2000cwc.googlegroups.com>

On Mar 13, 3:38 am, NetComrade <netcomradeNS..._at_bookexchange.net> wrote:
> On Thu, 01 Feb 2007 04:00:01 -0500, NetComrade
>
>
>
> <netcomradeNS..._at_bookexchange.net> wrote:
> >The snapshot log has been cleaned up from 8mil rows.. (old snapshot
> >references removed)
>
> >Is there are a way to do it, w/o upsetting the snapshots (there are a
> >lot)? (w/o dropping the snapshot log)
>
> >Theoretically.. it's just a table, right?
> >http://orafaq.com/node/4
>
> >system_at_VCRS> select count(*) from vcrs.MLOG$_MEMBER_SUMMARY;
>
> > COUNT(*)
> >----------
> > 7
>
> >Elapsed: 00:00:11.96
>
> >Statistics
> >----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 124083 consistent gets
> > 118200 physical reads
>
> This seems to do the trick:
>
> lock table MLOG$_MEMBER_SUMMARY in exclusive mode;
> drop table g;
> create table g as select * from MLOG$_MEMBER_SUMMARY;
> Truncate table MLOG$_MEMBER_SUMMARY;
> Insert into MLOG$_MEMBER_SUMMARY select * from g;
> drop table g;
>
> vcrs_at_VCRS> select count(*) from vcrs.MLOG$_MEMBER_SUMMARY;
>
> COUNT(*)
> ----------
> 11
>
> 167 consistent gets
>
> .......
> We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc)
> remove NSPAM to email

Your method can result in missing log entries. As soon as you drop table g; , the lock on mat view log gets released (since ddl does a commit).
You should in future do the following:

session1> LOCK table <master table> in exclusive mode;

           The above will prevent any entries to be placed
           in master table and hence prevent any entries to mlog.

session2> create table g as select * from <mlog table>;
session2> truncate table <mlog table>;

session2> insert /*+ append */ into <mlog table> select * from g; session2> commit;

session1> commit; or exit; to release the lock

Anurag Received on Tue Mar 13 2007 - 09:16:32 CDT