Re: reorganize snapshot log

On 13 Mar 2007 07:16:32 -0700, "Anurag Varma" <avoracle_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 Received on Tue Mar 13 2007 - 12:36:25 CDT