Locked Objects and Lock Holders in Oracle
Shows which sessions hold locks on which objects. Gives object-level diagnosis for DDL and row lock issues.
SELECT lo.session_id AS sid, s.serial#, s.username, s.status, s.machine,
o.owner, o.object_name, o.object_type,
DECODE(lo.locked_mode, 1,'NULL',2,'ROW SHARE',3,'ROW EXCL',4,'SHARE',5,'SHARE ROW EXCL',6,'EXCLUSIVE') AS lock_mode
FROM v$locked_object lo
JOIN dba_objects o ON o.object_id = lo.object_id
JOIN v$session s ON s.sid = lo.session_id
ORDER BY o.owner, o.object_name;Verify in a staging environment prior to running in production. This is a read-only diagnostic query; it does not acquire exclusive locks.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 11g).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Engineering Triad: Related Runbooks & Incident Response
Which production incidents and emergency drills execute this script as a diagnosis step?
Other ORACLE Diagnostic Scripts
View All (100)Oracle Active Sessions and Wait Events
Lists active user sessions with the running SQL_ID and wait event. Shows the live picture during performance issues.
Find Blocking Sessions in Oracle
Shows sessions that make others wait, with wait times. Identifies the root session to terminate in lock incidents.
Oracle Tablespace Usage Including Autoextend
Calculates tablespace usage against the real maximum, honoring autoextend limits. Catches space errors like ORA-01653 before they happen.
Top 20 Most Expensive Oracle SQL with v$sqlstats
Shows costliest SQL by elapsed time with executions, CPU and I/O. Tells you which SQL_ID to investigate for tuning.