Databases Reference
In-Depth Information
6.
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;
7.
Verify the object on which the lock is held:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_ID = 74136;
8.
Query the
V$LOCKED_OBJECT
dynamic performance view:
SELECT * FROM V$LOCKED_OBJECT;
9.
Query
DBA_WAITERS
to see which sessions are waiting due to a lock held by another
session:
SELECT * FROM DBA_WAITERS;
10. Query
DBA_BLOCKERS
to see which sessions are holding a lock that is blocking
another session:
SELECT * FROM DBA_BLOCKERS;
11. Execute the
utllockt.sql
script to view a tree representation of the current state
of the locks:
@$ORACLE_HOME/rdbms/admin/utllockt.sql
12. 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;
