Aller au contenu principal

Locks Management

· Une minute de lecture
Franck Blettner
Franck Blettner
Creator

Diagnostic queries for finding and resolving Oracle locks on the current session and across the database.

1. Gather locks for current session​

SELECT
  (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
  a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial,
 ' is blocking ',
  (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
  b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial
FROM 
  v$lock a, v$lock b
WHERE 
  a.block = 1
  AND b.request > 0
  AND a.id1 = b.id1
  AND a.id2 = b.id2;