RE: Rebuilding MLOG tables

I don't think it will do quite what you want it to, since the 'alter table move' statements are DDL so will release the lock.

I just tried this experiment in 2 SQLPlus windows:

SQL 1.1> lock table blah in exclusive mode; returns with
Table(s) Locked.

SQL 2.1> insert into blah values (1);
(this waits)

SQL 1.2> alter table summ_snapshot move tablespace tools_data; returns with
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

but this is enough to release the first lock, because 2.1 now returns with 1 row created.

On the other hand the move should be harmless. I see someone else has given a response with the same ultimate moral, but I'll still post, to point out the lock behavior.

> -----Original Message-----
> From: Stephen Lee [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 12, 2003 3:55 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Rebuilding MLOG tables
>
>
>
> Among some of the "Rube Goldberg" applications around here,
> is one that has
> multiple replication clients that subscribe to a master. For whatever
> reason, we might have a client not update for a while and the
> MLOG table(s)
> get big. Then, after that, every update has to read up to
> sky-high high
> water mark. From the looking around we have done, the thing
> to do to get
> the HWM back down without rattling replication seems to be
> (during a time
> when we know no updates are going into the master):
>
> lock table xyz in exclusive mode;
> alter table mlog$_xyz move tablespace over_there;
> alter table mlog$_xyz move tablespace back_here; (optional, I suppose)
> rollback; (release the lock)
>

LEGAL NOTICE:
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Sarnowski, Chris
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).