oracle-wiki.net - Blocking Locks Verbose

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

Blocking Locks Verbose (General All Rounder and Quite Lightweight)

--

-- Blocking Locks Verbose

--

-- Shows blocked session, blocking session and details of the programs and machines they are running from.

-- In addition, shows the CTIME value which details how long the blocking session has had hold of the lock

--

-- Best run from SQL Developer where the output is formatted. Running from SQLPLUS makes it hard to read

-- due to the verbose nature of the query.

--

SELECT

sysdate,

c.blocker_inst,

c.blocker_sid,

c.blocker_username,

c.blocker_sql_id,

c.blocker_program,

c.blocker_machine,

c.blocker_info,

c.blocker_action,

c.blocker_client,

c.blockee_inst,

c.blockee_sid,

c.blockee_serial,

c.blockee_username,

c.blockee_sql_id,

c.blockee_program,

c.blockee_machine,

c.blockee_action,

c.blockee_info,

c.blockee_client,

d.os_user_name locked_os_user_name,

d.oracle_username locked_username,

b.owner object_owner,

b.object_name,

b.object_type,

c.ctime "Time Locked (s)"

FROM

gv$locked_object d,

dba_objects b,

(

SELECT

( SELECT username FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_username,

( SELECT sql_id FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_sql_id,

( SELECT program FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_program,

( SELECT machine FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_machine,

( SELECT action FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_action,

( SELECT client_info FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_info,

( SELECT client_identifier FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_client,

y.sid blocker_sid,

y.inst_id blocker_inst,

( SELECT username FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_username,

( SELECT sql_id FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_sql_id,

( SELECT program FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_program,

( SELECT machine FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_machine,

( SELECT action FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_action,

( SELECT client_info FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_info,

( SELECT client_identifier FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_client,

z.sid blockee_sid,

(select serial# from gv$session where sid=z.sid) blockee_serial,

z.inst_id blockee_inst,

z.ctime

FROM

gv$lock y,

gv$lock z

WHERE

y.block = 1 AND

z.request > 0 AND

y.id1 = z.id1 AND

y.id2 = z.id2

) c

WHERE

d.OBJECT_ID = b.OBJECT_ID AND

d.inst_id = c.blockee_inst AND

d.session_id = c.blockee_sid

/

Notes on Blocking Locks (Verbose)

Purpose

Identifies blocking sessions and the sessions they are blocking, along with the program, machine, module, action, client identifier and current SQL for both sides of the lokc (Blockers and Waiters). It also shows the the length of time the lock has been held. The query can be used on RAC, as each row shows the INST_ID.

This is a good general purpose locking script. Unlike the ROWID script, it does not `show the row involved, only the object, so runs much faster, even when the system is under heavy load.

Using this Script

This is my go to script for general locking investigations. It is lightweight and can be run repeatedly, even at short intervals. Because it identifies the object rather than the specific row, I use it when I need a quick view of who is blocking whom and what table is involved. If you then need to pin down the exact row being locked, then use the ROWID script.

Key Column Definations and Explanations

BLOCKER_SID / BLOCKER_INST

Session and instance holding the lock

BLOCKEE_SID / BLOCKEE_INST

Session and instance waiting for the lock

BLOCKER_SQL_ID

SQL currently running in the blocking session

BLOCKEE_SQL_ID

SQL currently blocked

OBJECT_NAME / OBJECT_TYPE / OBJECT_OWNER

The table or object the blockee is waiting on

LOCKED_USERNAME / LOCKED_OS_USER_NAME

Database and OS user associated with the locked object entry

Time Locked (s)

How long the blocking session has held the lock (CTIME)

Best Practice

Run this script from Oracle SQL Developer, where the wide output is easier to read. It works in SQL*Plus, but the large number of columns makes the results difficult to interpret without additional formatting. Suitable for both RAC and single instance databases.

Published on

Published 25th September 2026