外键无索引导致oracle数据库锁的经典问题
sys@HRTESTDB(rhel5)>sys@HRTESTDB(rhel5)>SELECT a.sid || 2 decode(request, 3 0, 4 ' :holder',' :Waiter') sess_id,blocking_session blocker, 5 lmode, 6 request, 7 a.type, 8 c.object_name, 9 decode(row_wait_obj#, 10 -1, 11 'Holder of Lock !!!', 12 dbms_rowid.rowid_create(1, 13 row_wait_obj#, 14 row_wait_file#, 15 row_wait_block#, 16 row_wait_row#)) row_id, 17 nvl(SQL_FULLTEXT, 'Holder of Lock !!!') sqltext 18 FROM V$LOCK A, V$LOCKED_OBJECT B, ALL_OBJECTS C, V$SESSION D, V$SQL E 19 WHERE (id1, id2, a.type) in 20 (select id1, id2, type from v$lock where request > 0) 21 AND a.sid = b.session_id 22 AND b.object_id = c.object_id 23 AND d.sid = a.sid 24 AND d.sql_hash_value = e.hash_value(+);SESS_ID BLOCKER LMODE REQUEST TY OBJECT_NAME ROW_ID SQLTEXT--------------- ---------- ---------- ---------- -- ------------------------------ ------------------ --------------------------------------------------364 :holder 3 0 TM T_F Holder of Lock !!! Holder of Lock !!!364 :holder 3 0 TM T_P Holder of Lock !!! Holder of Lock !!!124 :Waiter 364 3 5 TM T_F Holder of Lock !!! delete t_p where id = 2124 :Waiter 364 3 5 TM T_P Holder of Lock !!! delete t_p where id = 2
