Dealing with lock contention

Inspect the active locks:

SELECT SID, ID1, ID2, BLOCK, TYPE, LMODE, REQUEST, CTIME
FROM V$LOCK
MINUS
SELECT SID, ID1, ID2, BLOCK, TYPE, LMODE, REQUEST, CTIME
FROM V$ENQUEUE_LOCK;

Verify the object on which the lock is held:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_ID = 74136;

Query the V$LOCKED_OBJECT dynamic performance view:

SELECT * FROM V$LOCKED_OBJECT;

Query DBA_WAITERS to see which sessions are waiting due to a lock held by another session:

SELECT * FROM DBA_WAITERS;

Query DBA_BLOCKERS to see which sessions are holding a lock that is blocking another session:

SELECT * FROM DBA_BLOCKERS;

Execute the utllockt.sql script to view a tree representation of the current state of the locks:

@$ORACLE_HOME/rdbms/admin/utllockt.sql

View the object, file, block, and row locked:

SELECT
SUBSTR(F.NAME, 1, 40) AS FILE_NAME,
O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE,
S.ROW_WAIT_BLOCK#, S.ROW_WAIT_ROW#
FROM V$SESSION S, V$DATAFILE F, ALL_OBJECTS O
WHERE S.SID = 17
AND S.ROW_WAIT_FILE# = F.FILE#
AND S.ROW_WAIT_OBJ# (+)= O.OBJECT_ID;

Acquire the SERIAL# and SID of the locked session (SESSION2):

SELECT SERIAL# FROM V$SESSION WHERE SID = 17;

Kill the locked session:

ALTER SYSTEM KILL SESSION '17, 15';

Complete the transaction in SESSION1 to avoid other locking:

-- SESSION 1
ROLLBACK;

Find who is blocking who

select s1.username || '@' || s1.machine
 || ' ( SID=' || s1.sid || ' ) is blocking '
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
 from v$lock l1, v$session s1, v$lock l2, v$session s2
 where s1.sid=l1.sid and s2.sid=l2.sid
 and l1.BLOCK=1 and l2.request > 0
 and l1.id1 = l2.id1
 and l2.id2 = l2.id2 ;