Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: reorganize snapshot log

Re: reorganize snapshot log

From: Valentin Minzatu <valentinminzatu_at_yahoo.com>
Date: 13 Mar 2007 11:21:47 -0700
Message-ID: <1173810107.637094.302630@64g2000cwx.googlegroups.com>


On Mar 13, 1:36 pm, NetComrade <netcomradeNS..._at_bookexchange.net> wrote:
> On 13 Mar 2007 07:16:32 -0700, "Anurag Varma" <avora..._at_gmail.com>
> wrote:
>
>
>
>
>
> >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
>
> Doh...
>
> I thought of DDL prior to doing this, but I guess later on I let an
> unnecessary DDL sneak in.
>
> I shouldn't have dropped the table as part of my script. Truncate is
> DDL too, however, that wouldn't worry me much, b/c I believed (maybe
> wrong) any change to the master table is an insert to the LOG table.
>
> Your method is definetely cleaner (and takes away any guessing on what
> happens in the background).. will use it next time a database goes
> away w/o dropping the snapshot (and cleaning out it's entry in mlog)
>
> thanks for your feedback, now I have to go and verify the snapshots
> (not that they're critical)
> .......
> We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc)
> remove NSPAM to email- Hide quoted text -
>
> - Show quoted text -

Have you tried ALTER TABLE MLOG$_MEMBER_SUMMARY MOVE? I think it should work - I do not have a test bed to try it out. Received on Tue Mar 13 2007 - 13:21:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US