Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> why would enable constraint cause a delete to wait on library cache lock?
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).Received on Mon Jan 12 2004 - 16:29:25 CST
![]() |
![]() |