Re: reorganize snapshot log

On Mar 14, 10:43 am, "Valentin Minzatu" <valentinminz..._at_yahoo.com> wrote:
> On Mar 13, 3:48 pm, NetComrade <netcomradeNS..._at_bookexchange.net>
> wrote:
>
>
>
> > >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.
>
> > I haven't tried it, b/c I believed it was not supported on snapshot
> > logs (prob b/c snapshot log already has triggers populating it, and
> > any move operation might create a temporary table populating it with
> > yet another set of triggers). The orafaq link above states that you
> > can't do 'table redefinition' on snapshot logs among other things.
>
> > Just tried it with another log on a table which hasn't been replicated
> > in over a year (nor populated), doesn't work:
>
> > system_at_VCRS> alter table vcrs.MLOG$_BOUNCES move payproductsdat;
> > alter table vcrs.MLOG$_BOUNCES move payproductsdat
> > *
> > ERROR at line 1:
> > ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
>
> > .......
> > We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc)
> > remove NSPAM to email
>
> I am not sure what is the definition for your log (and what version of
> Oracle you are using), but I have just tested with 10R1 and it worked.

Ah .. you state a good point. One can use alter table move also. The documentation however suggests using the other method. http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14227/rarmanmv.htm#i30379

But I don't see why alter table move will not work. There might be some things one might have to consider if someone has created indexes on mview log for performance which would need to be rebuilt after alter table move.

However, even with alter table move .. one would need to lock the <master table> otherwise one might get
ORA-00054: resource busy and acquire with NOWAIT specified if there are transactions happening on master table while this is being attempted.

Anurag Received on Wed Mar 14 2007 - 10:56:32 CDT