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: [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). > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
