Look up tables' lock

look up the table which is locked.

SELECT P.SPID,

       A.SERIAL#,

       C.OBJECT_NAME,

       B.SESSION_ID,

       B.ORACLE_USERNAME,

       B.OS_USER_NAME

  FROM V$PROCESS P, V$SESSION A, V$LOCKED_OBJECT B, ALL_OBJECTS C

 WHERE P.ADDR = A.PADDR

   AND A.PROCESS = B.PROCESS

   AND C.OBJECT_ID = B.OBJECT_ID;

SELECT SESS.SID,

       SESS.SERIAL#,

       LO.ORACLE_USERNAME,

       LO.OS_USER_NAME,

       AO.OBJECT_NAME,

       LO.LOCKED_MODE

  FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS

 WHERE AO.OBJECT_ID = LO.OBJECT_ID

   AND LO.SESSION_ID = SESS.SID;

 

look up some one who causes the dead lock, one user or one process

SELECT B.OWNER, B.OBJECT_NAME, L.SESSION_ID, L.LOCKED_MODE

  FROM V$LOCKED_OBJECT L, DBA_OBJECTS B

 WHERE B.OBJECT_ID = L.OBJECT_ID;

SELECT *

  FROM V$SESSION T1, V$LOCKED_OBJECT T2

 WHERE T1.SID = T2.SESSION_ID;