On Mar 14, 12:24 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> On Mar 14, 12:12 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com>
> wrote:
>
>
>
>
>
> > On Mar 14, 11:56 am, "Anurag Varma" <avora..._at_gmail.com> wrote:
>
> > > 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/r...
>
> > > 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > I do not see how would that improve anything: if the mv log is locked
> > that implies there is at least one lock on the master ...
>
> Well lock table <master table> will wait for pending transactions to
> complete and then lock the table .. so that you can do the alter table
> <mviewlog> move;
>
> If you don't do that and if transactions keep happening, you'd just
> need
> to keep trying if the master table is getting a lot of transactions.
>
> Anurag- Hide quoted text -
>
> - Show quoted text -
The same logic applies to the MV log, doesn't it? If there are
transactions against the master table you can't lock either, if there
aren't then you can lock any, so no need to bother locking the master
table.
Received on Wed Mar 14 2007 - 11:50:36 CDT
