Re: large matview log, does it lock base table when dropping?

From: Yong Huang <>
Date: Fri, 1 Aug 2008 07:25:55 -0700 (PDT)
Message-ID: <>

> Date: Thu, 31 Jul 2008 10:32:51 -0500
> From: Dan Norris <>
> Subject: large matview log, does it lock base table when dropping?
> We've got a large materialized view log (100Gb) on a 1.7 Gb table in
> We need to drop the log, but we aren't sure if dropping the log
> will put any sort of lock on the base table. We're going to try to test
> it, but we don't have too many other logs that are large enough to take
> time to drop so we can inspect the state of the locking during the drop.
> Has anyone done this before to see exactly what structures are locked
> during the drop? In theory, since the log is being dropped, there's no
> reason to lock the base table since we don't care about capturing
> additional changes (that would normally be logged).
> Thanks in advance,
> Dan

I tested by setting event 10704 at level 10 (Oracle Dropping the materialized view took 37 TM locks in mode 3 (SX) on lots of objects, and 1 mode 6 (exclusive) lock on the materialized view log table (MLOG$_<base table name>). You can grep "^ksqgtl \*\*\* TM" on the trace file. Other locks are CU, TX, MD, etc. My base table is only 55M so dropping the mview log took only a few seconds on my laptop (even after a lot of updates). You can experiment with a bigger table and mview log and view the sequence of locks taken with event 10704. Timing info is in the trace file too.

Yong Huang       

Received on Fri Aug 01 2008 - 09:25:55 CDT

Original text of this message