Re: reorganize snapshot log

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. Received on Wed Mar 14 2007 - 13:57:22 CDT