Here is a simple SQL to identify locking sessions within an Oracle Database:
SELECT 'SID ' || L1.SID ||' is blocking ' || L2.SID BLOCKING
FROM V$LOCK L1, V$LOCK L2
WHERE L1.BLOCK =1 AND L2.REQUEST > 0
AND L1.ID1=L2.ID1
AND L1.ID2=L2.ID2
Here is a sample output:
SID 776 is blocking 465
All locking sessions are stored in the table V$LOCK.
Note:
The locking can happen due to any reason. It is not necessarily caused
due to Applications or User Activity. Look everywhere possible to
identify the problem. Likely places to look first ... Concurrent
program, running for a long time, user FORM Sessions, SQL*Plus or TOAD
sessions with ROWID selection or UPDATEs and the current session is not COMMITed, Running SQL Scripts, DataLoad jobs, etc are just to name a few.
