My OraFAQ Blog

Contact me

Locks can be monitored using v$lock or v$locked_object. The following query combines the best of both options. The "block" column indicates that a session is blocking.

You can use this query to identify blocking locks, or to examine all locks in case of a complex locking scenario (eg. session A blocks session B; session B blocks session C; ...)

select /*+ rule */ se.USERNAME, se.osuser, l.sid, l.type, 
l.id1, l.id2, lmode, request, block, do.OBJECT_NAME, do.owner 
from v$lock l, dba_objects do, v$session se 
where l.sid>5
and l.sid=se.sid and l.id1=do.object_id(+)
order by block desc, l.sid;

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...