Show Oracle Locks

If you’re having issues with locks in Oracle, here’s a way to show what sessions have locks on which objects.

select s.osuser "O/S-User",
  s.username "Ora-User",
  s.sid "Session-ID",
  s.serial# "Serial",
  s.process "Process-ID",
  s.status "Status",
  l.name "Obj Locked",
  l.mode_held "Lock Mode",
  count(*)
from v$session s, dba_dml_locks l, v$process p
where l.session_id = s.sid and p.addr = s.paddr
group by s.osuser, s.username, l.name, l.mode_held, s.sid, s.serial#, s.process, s.status,
l.name, l.mode_held
order by 1, 2;