Oracle Locked Objects Report
This script lists all currently locked objects in the database. It joins V$LOCKED_OBJECT, DBA_OBJECTS, and V$SESSION to display session details, object information, lock modes, and OS user data, helping DBAs identify blocking sessions and locking issues.
oraclesqlmonitoring-alertsv1.0.0
0 stars0 downloads17 views0 comments
By OracleDba • Created
Code
(39 lines)123456789101112131415161718192021222324252627282930313233343536373839
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/locked_objects.sql
-- Author : DR Timothy S Hall
-- Description : Lists all locked objects.
-- Requirements : Access to the V$ views.
-- Call Syntax : @locked_objects
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT lo.session_id AS sid,
s.serial#,
NVL(lo.oracle_username, '(oracle)') AS username,
o.owner AS object_owner,
o.object_name,
Decode(lo.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
lo.locked_mode) locked_mode,
lo.os_user_name
FROM v$locked_object lo
JOIN dba_objects o ON o.object_id = lo.object_id
JOIN v$session s ON lo.session_id = s.sid
ORDER BY 1, 2, 3, 4;
SET PAGESIZE 14
SET VERIFY ON