On Mar 14, 1:01 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
lock table will indeed lock the table, but alter table (being a ddl)
will commit the preceeding transaction and the (lock table one) and
you are back to where this started, are you not?
Received on Wed Mar 14 2007 - 12:46:10 CDT
