reorganize snapshot log
NetComrade
references removed)
Is there are a way to do it, w/o upsetting the snapshots (there are a
lot)? (w/o dropping the snapshot log)
Theoretically.. it's just a table, right?
http://orafaq.com/node/4
system@VCRS> select count(*) from vcrs.MLOG$_MEMBER_SUMMARY;
COUNT(*)
----------
7
Elapsed: 00:00:11.96
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
124083 consistent gets
118200 physical reads
.......
We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc)
remove NSPAM to email
NetComrade
<netcomr...@bookexchange.net> wrote:
>The snapshot log has been cleaned up from 8mil rows.. (old snapshot
>references removed)
>
>Is there are a way to do it, w/o upsetting the snapshots (there are a
>lot)? (w/o dropping the snapshot log)
>
>Theoretically.. it's just a table, right?
>http://orafaq.com/node/4
>
>system@VCRS> select count(*) from vcrs.MLOG$_MEMBER_SUMMARY;
>
> COUNT(*)
>----------
> 7
>
>Elapsed: 00:00:11.96
>
>Statistics
>----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 124083 consistent gets
> 118200 physical reads
>
This seems to do the trick:
lock table MLOG$_MEMBER_SUMMARY in exclusive mode;
drop table g;
create table g as select * from MLOG$_MEMBER_SUMMARY;
Truncate table MLOG$_MEMBER_SUMMARY;
Insert into MLOG$_MEMBER_SUMMARY select * from g;
drop table g;
vcrs@VCRS> select count(*) from vcrs.MLOG$_MEMBER_SUMMARY;
COUNT(*)
----------
11
167 consistent gets
Anurag Varma
wrote:
Your method can result in missing log entries. As soon
as you drop table g; , the lock on mat view log gets released
(since ddl does a commit).
You should in future do the following:
session1> LOCK table <master table> in exclusive mode;
The above will prevent any entries to be placed
in master table and hence prevent any entries to mlog.
session2> create table g as select * from <mlog table>;
session2> truncate table <mlog table>;
session2> insert /*+ append */ into <mlog table> select * from g;
session2> commit;
session1> commit; or exit; to release the lock
Anurag
NetComrade
wrote:
Doh...
I thought of DDL prior to doing this, but I guess later on I let an
unnecessary DDL sneak in.
I shouldn't have dropped the table as part of my script. Truncate is
DDL too, however, that wouldn't worry me much, b/c I believed (maybe
wrong) any change to the master table is an insert to the LOG table.
Your method is definetely cleaner (and takes away any guessing on what
happens in the background).. will use it next time a database goes
away w/o dropping the snapshot (and cleaning out it's entry in mlog)
thanks for your feedback, now I have to go and verify the snapshots
(not that they're critical)
Valentin Minzatu
wrote:
> On 13 Mar 2007 07:16:32 -0700, "Anurag Varma" <avora...@gmail.com>
>
> - Show quoted text -
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.
NetComrade
>
>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@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
Valentin Minzatu
wrote:
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.
Valentin Minzatu
wrote:
>
> - Show quoted text -
I think I know why it didn't work for you. Do not specify the "new"
tablespace name. Just do:
alter table vcrs.MLOG$_BOUNCES move;
Valentin Minzatu
>
> - Show quoted text -
One more thing: should you want to move the table to a different
tablespace, then use ALTER TABLE <mv_log_name> MOVE TABLESPACE
<tbspc_name>;
Anurag Varma
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/rarmanmv.htm#i30379
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
Valentin Minzatu
>
> 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.
>
>
> - 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 ...
Anurag Varma
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
Valentin Minzatu
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.
Anurag Varma
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
Valentin Minzatu
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?
Anurag Varma
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...
Valentin Minzatu
>
> - 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?
Anurag Varma
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.
Valentin Minzatu
>
> - Show quoted text -
I got you now - too long of day :)