Re: reorganize snapshot log

On Thu, 01 Feb 2007 04:00:01 -0500, NetComrade <netcomradeNSPAM_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 Received on Tue Mar 13 2007 - 02:38:10 CDT