Did you loon into v$lock? V$LOCK has columns REQUEST and LMODE and it would tell you the mode
of the lock imposed by alter table. I would guess that "enable validate" would briefly
impose a shared lock on the whole table.
On 01/12/2004 05:29:25 PM, Jacques Kilchoer wrote:
> I am only an egg, so I hope someone else can explain this to me.
>
> Oracle Enterprise Edition 8.1.7.4.1 on Windows 2000
>
> I was experimenting to see if an "alter table enable validate constraint" would cause DML statements to wait. I thought it wouldn't. But in real life I see something different.
>
> I have a table X (object_id 429995) with about 100 million rows. In one session (sid 15) I enable a check constraint, and in another session (sid 14) I simultaneously delete a few rows from the table. The delete "waits" on the enable constraint to complete, and it's waiting on a library cache lock. Why would that be? At the end of this e-mail you can see the contents of DBA_LOCKS, V$LOCKED_OBJECT, V$SESSION_WAIT. I read the system state dump and I still don't understand why there would be a library cache lock.
>
> Session ID 15
> --- sid-15-SQL1
> alter table hes_a_keeper.many_rows add
> (constraint ck1 check (dummy_column > 'B') disable) ;
> --- sid-15-SQL2 (simultaneous with sid-14-SQL1)
> alter table hes_a_keeper.many_rows enable validate constraint ck1 ;
>
> Session ID 14
> --- sid-14-SQL1 (simultaneous with sid-15-SQL2)
> delete from hes_a_keeper.many_rows where rownum < 10 ;
>
> Session ID 10
> --- statements issued while sid-14-SQL1 and sid-15-SQL2 are running
> alter session set events 'immediate trace name systemstate level 10' ;
> select * from dba_locks where session_id in (14,15) ;
> select * from v$locked_object where object_id = 429995 ;
> select * from v$session_wait where sid in (14,15) ;
>
>
> SQL> select * from dba_locks where session_id in (14,15) ;
> SESSION_ID LOCK_TYPE MODE_HELD
> ---------- -------------------------- ----------------------------------------
> MODE_REQUESTED LOCK_ID1
> ---------------------------------------- ----------------------------------------
> LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
> ---------------------------------------- ------------ ----------------------------------------
> 15 DML Share
> None 429995
> 0 10 Not Blocking
>
>
> SQL> select * from v$locked_object where object_id = 429995 ;
> XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME
> --------- --------- --------- --------- ---------- ------------------------------
> OS_USER_NAME PROCESS LOCKED_MODE
> ------------------------------ --------- -----------
> 0 0 0 429995 15 JRK
> jkilchoe 1540:260 4
>
>
> SQL> select * from v$session_wait where sid in (14,15) ;
> SID SEQ# EVENT
> --------- --------- ----------------------------------------------------------------
> P1TEXT P1 P1RAW
> ---------------------------------------------------------------- --------- --------
> P2TEXT P2 P2RAW
> ---------------------------------------------------------------- --------- --------
> P3TEXT P3 P3RAW WAIT_TIME
> ---------------------------------------------------------------- --------- -------- ---------
> SECONDS_IN_WAIT STATE
> --------------- -------------------
> 15 5005 db file scattered read
> file# 13 0000000D
> block# 19516 00004C3C
> blocks 8 00000008 0
> 0 WAITING
>
> 14 142 library cache lock
> handle address 47483828 02D48BB4
> lock address 40114204 0264181C
> 10*mode+namespace 21 00000015 0
> 9 WAITING
>
> SQL>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jacques Kilchoer
> INET: Jacques.Kilchoer_at_quest.com
>
> 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: ListGuru_at_fatcity.com (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).
>
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: mladen_at_wangtrading.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Jan 12 2004 - 16:44:31 CST
