Finding Lock In Oracle Database using SQL Statements.

1. Find All Object Which are Currently Locked By Session.

SELECT OBJECT_NAME,S.INST_ID,S.SID,S.SERIAL#,S.OSUSER,S.SERVER,S.MACHINE, S.STATUS,P.PNAME,SQL_TEXT, SQL_FULLTEXT
FROM GV$LOCKED_OBJECT L,GV$SESSION S,GV$PROCESS P,DBA_OBJECTS O, GV$SQLAREA T
WHERE L.OBJECT_ID=O.OBJECT_ID
AND S.SQL_ADDRESS =T.ADDRESS
AND S.SQL_HASH_VALUE =T.HASH_VALUE
AND L.SESSION_ID=S.SID
AND S.PADDR=P.ADDR;

2.  Lists all DML locks and all outstanding requests for a DML lock.

SELECT * FROM DBA_DML_LOCKS;

3. Holding a lock on an object for which another session is waiting.

SELECT * FROM DBA_BLOCKERS;

4. Blocking Session Details.

SELECT BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS,SECONDS_IN_WAIT
FROM  V$SESSION
WHERE  BLOCKING_SESSION IS NOT NULL
ORDER BY BLOCKING_SESSION;