Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: reorganize snapshot log
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>;
session1> commit; or exit; to release the lock
Anurag Received on Tue Mar 13 2007 - 09:16:32 CDT
![]() |
![]() |