–Identifying where locks are being held
–The following will identify the locks that have been held for the longest time along with the blocking/blocked session:
SELECT
lr.sid blocker_sid,
ld.sid blocked_sid,
lr.type, — Type will typically be TM, TX, UL. Otherwise it is system type.
lt.name,
lr.id1, — The value contained in these varies. For TM ID1 contains the object_id, for TX it is the Rollback entry
lr.id2, — The value contained in these varies. For TX it is the Transaction entry.
decode(lr.lmode,
0,’none(0)’,
1,’null(NULL)(1)’,
2,’row-S(SS)(2)’,
3,’row-S(SX)(3)’,
4,’Share(S)(4)’,
5,’S/Row-X(SSX)(5)’,
6,’exclusive(X)(6)’,
LTRIM(TO_CHAR(lr.lmode,’990′))) lock_hold_mode,
decode(lr.request,
0,’none(0)’,
1,’null(NULL)(1)’,
2,’row-S(SS)(2)’,
3,’row-S(SX)(3)’,
4,’Share(S)(4)’,
5,’S/Row-X(SSX)(5)’,
6,’exclusive(X)(6)’,
LTRIM(TO_CHAR(lr.request,’990′))) lock_request_mode,
lr.ctime time_blocker_held,
ld.ctime time_blocked_waiting
FROM v$lock lr,
v$lock ld,
v$lock_type lt
WHERE lt.type (+) = lr.type
AND ld.id1 = lr.id1 — rollback entries match
AND ld.id2 = lr.id2 — transaction entries match
AND lr.block = 1 — blocker
AND ld.block = 0 — blocked
ORDER BY lr.ctime DESC;