On Mar 14, 2:57 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
> On Mar 14, 2:40 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com>
> wrote:
>
>
>
>
>
> > On Mar 14, 2:33 pm, "Anurag Varma" <avora..._at_gmail.com> wrote:
>
> > > On Mar 14, 1:46 pm, "Valentin Minzatu" <valentinminz..._at_yahoo.com>
> > > wrote:
>
> > > > 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?
>
> > > Well the lock table needs to be issued from a session which is
> > > separate
> > > from the alter table move.
> > > I believe I stated that way back in the thread...- Hide quoted text -
>
> > > - Show quoted text -
>
> > I am not sure I get your point. In order to get rid of the row level
> > locks you lock the whole table from a different session and then you
> > execute from yet a different session a DDL against the same table. Has
> > this scenario worked for you, because I can't see how the third
> > session (DDL one) can get around the exclusive lock that the second
> > session holds on the table (alter table lock session) in order to
> > prevent the first session from locking rows in the same table (the
> > session executing the inserts). Is there something I missed in your
> > explanation?
>
> Yes!!
> Did you notice I lock the MASTER TABLE ... and NOT the materialized
> view log table?
>
> So the concept is LOCK the master table so that no rows get to
> the materialized view log. That way you can do the alter table move on
> the materialzied view log.
>
> So if you'd read my response, I write "lock table TM in exclusive
> mode".
> TM is the MASTER TABLE. MLOG$_TM will be the materialized view log
> table.- Hide quoted text -
>
> - Show quoted text -
I got you now - too long of day :)
Received on Wed Mar 14 2007 - 14:00:08 CDT
