OracleBlocking & locks

Locked objects across all instances (three views)

Three looks at DML locks from GV$LOCKED_OBJECT: which objects are locked and by whom, the lock mode per object, and the same with the OS process ID and the SQL each locking session is running.

All RAC instances

Tested on 12c, 19c · verified Sep 2026

ora-locks-rac.sql

1CLEAR COLUMNS2SET LINESIZE 250 PAGESIZE 200 TRIMOUT ON TAB OFF LONG 40003 4-- 1. Locked objects and the sessions holding them5select6   c.object_name,7   c.object_type,8   b.sid,9   b.serial#,10   b.status,11   b.osuser,12   b.machine13from14   gv$locked_object a ,15   gv$session b,16   dba_objects c17where18   b.sid = a.session_id19and20   a.object_id = c.object_id;21 22-- 2. Lock mode per object23column oracle_username format a15;24column os_user_name format a15;25column object_name format a37;26column object_type format a37;27select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from28(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from gv$locked_object) a,29(select object_id, owner, object_name,object_type from dba_objects) b30where a.object_id=b.object_id;31 32-- 3. With OS process ID, program, logon time and full SQL text33SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,34S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT35FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S,36V$PROCESS P, V$SQL SQ37WHERE L.OBJECT_ID = O.OBJECT_ID38AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR39AND S.SQL_ADDRESS = SQ.ADDRESS;

CLEAR COLUMNS
SET LINESIZE 250 PAGESIZE 200 TRIMOUT ON TAB OFF LONG 4000

-- 1. Locked objects and the sessions holding them
select
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

-- 2. Lock mode per object
column oracle_username format a15;
column os_user_name format a15;
column object_name format a37;
column object_type format a37;
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from gv$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;

-- 3. With OS process ID, program, logon time and full SQL text
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

Save it as ora-locks-rac.sql and run it with SQL> @ora-locks-rac.

Open in denrepo

Helps with

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

More Oracle scripts: Blocking & locks

Blocked sessions and their blockersSessions waiting on another session, joined to the blocker. Both sides show as SID,SERIAL#, ready to paste into a kill. On RAC, also check…
Blocking treeThe whole chain as an indented tree, starting from the root blocker. When ten sessions are stuck, the one at the left margin is the one to deal with.
Locked objects and who holds themEvery object with a DML lock, the session holding it and the lock mode in plain words. Exclusive locks from sessions that are INACTIVE usually mean…