Re: reorganize snapshot log

On Mar 14, 12:50 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com> wrote:
> 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.

I don't understand your point.
Here try this:

1. create a table TM (master table) create table tm (a number, b number); alter table tm add constraint tm_pk primary key (a);
2. create materialized view log on it. create materialized view log on tm with primary key;
3. Run the following to simulate continuous transactions: begin for x in (select rownum a, rownum b from all_tables) loop insert into tm values (x.a,x.b); dbms_lock.sleep(2); commit; end loop; end; /
4. Now in another session try your alter table move. It will keep failing with ORA-00054 until the above completes .. or until you get lucky to be able to slip in alter table between the commit and next insert.
5. Now Run the transaction in #3 and this time issue the following from a separate session: lock table tm in exclusive mode;

   ... you'd be able to acquire the exclusive lock

       before the statement in #3 completes. And
       right after you acquire the lock you can
       run the alter table move.

Anurag Received on Wed Mar 14 2007 - 12:01:03 CDT