oracle-wiki.net - Blockers and Waiters with ROWID

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

Blockers and Waiters with ROWID (Heavyweight)

--

-- Blockers and Waiters with RowID

--

-- Shows blocked session, blocking session, RowIDs on the Object being Waited on 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

gvw.inst_id Waiter_Inst,

gvw.sid Waiter_Sid,

gvs_w.osuser waiter_osuser,

gvs_w.program waiter_program,

gvs_w.machine waiter_machine,

gvs_w.client_identifier waiter_identifer,

gvs_w.client_info waiter_thread,

gvs_w.seconds_in_wait waiter_secs_in_wait,

gvs_w.sql_id waiter_sql,

dbms_rowid.rowid_create(

1,

gvs_w.ROW_WAIT_OBJ#,

gvs_w.ROW_WAIT_FILE#,

gvs_w.ROW_WAIT_BLOCK#,

gvs_w.ROW_WAIT_ROW#

) waiter_rowid_Waiting_on,

gvs_w.event waiter_event,

decode(gvw.request,

0, 'None',

1, 'NoLock',

2, 'Row-Share',

3, 'Row-Exclusive',

4, 'Share-Table',

5, 'Share-Row-Exclusive',

6, 'Exclusive',

'Nothing-') Waiter_Mode_Req,

decode(gvh.type,

'AE', 'Edition Enqueue',

'AT', 'Lock held for the ALTER TABLE statement',

'BL', 'Buffer hash table instance',

'CF', 'Control file schema global enqueue',

'CI', 'Cross-instance function invocation instance',

'CU', 'Cursor bind',

'DF', 'datafile instance',

'DL', 'Direct loader parallel index create',

'DM', 'Mount/startup db primary/secondary instance',

'DR', 'Distributed recovery process',

'DX', 'Distrted_Transaxion',

'FS', 'File set',

'HW', 'Space management operations on a specific segment',

'IN', 'Instance number',

'IR', 'Instance recovery serialization global enqueue',

'IS', 'Instance state',

'IV', 'Library cache invalidation instance',

'JQ', 'Job queue',

'KK', 'Thread kick',

'MM', 'Mount definition global enqueue',

'MR', 'Media_recovery',

'PF', 'Password File',

'PI', 'Parallel Operation',

'PS', 'Parallel Operation',

'PR', 'Process startup',

'RT', 'Redo thread global enqueue',

'SC', 'System change number instance',

'SM', 'SMON',

'SN', 'Sequence number instance',

'SQ', 'Sequence number enqueue',

'SS', 'Sort segment',

'ST', 'Space transaction enqueue',

'SV', 'Sequence number value',

'TA', 'Generic enqueue',

'TS', 'Temporary segment enqueue (ID2=0) or New block allocation enqueue (ID2=1)',

'TT', 'Temporary table enqueue',

'UN', 'User name',

'US', 'Undo segment DDL',

'WL', 'Being-written redo log instance',

'TX', 'Transaction (Left for backwards compatability)',

'TM', 'Dml (Left for backwards compatability)',

'UL', 'PLSQL User_lock (Left for backwards compatability)',

'LS', 'LogStaartORswitch (Left for backwards compatability)',

'RW', 'Row_wait (Left for backwards compatability)',

'TE', 'Extend_table (Left for backwards compatability)',

'Nothing or Library cache lock instance lock (LA..LP) or Library cache pin instance (NA..NZ)') Waiter_Lock_Type,

gvh.inst_id Locker_Inst,

gvh.sid Locker_Sid,

gvs.osuser locker_osuser,

gvs.machine locker_machine,

gvs.program locker_program,

gvs.client_identifier locker_identifer,

gvs.client_info locker_thread,

gvs.seconds_in_wait locker_secs_in_wait,

gvs.serial# Locker_Serial,

gvs.event locker_event,

gvs.sql_id locker_sql,

gvs.prev_sql_id locker_prev_sql,

gvs.status locker_Status,

gvs.module locker_Module,

gvs_w.row_wait_obj# object_locked,

gvh.ctime secs_object_locked

FROM gv$lock gvh,

gv$lock gvw,

gv$session gvs,

gv$session gvs_w

WHERE (gvh.id1, gvh.id2)

IN (SELECT

id1,

id2

FROM gv$lock

WHERE request=0

INTERSECT

SELECT

id1,

id2

FROM gv$lock

WHERE lmode=0

)

AND gvh.id1=gvw.id1

AND gvh.id2=gvw.id2

AND gvh.request=0

AND gvw.lmode=0

AND gvh.sid=gvs.sid

AND gvw.sid=gvs_w.sid

AND gvh.inst_id=gvs.inst_id

AND gvw.inst_id=gvs_w.inst_id

AND gvs_w.sql_id is not null

/

Notes on Blockers and Waiters with ROWID

Purpose

This script identifies Oracle sessions that are waiting on locks and the sessions holding those locks. Useful for row-level (TX) locking contention, where Session A is genuinely blocking Session B on a specific row, rather than some other lock contention.

The script displays both sides of the blocking lock (Blockee and Blocker), along with the SQL being executed, client information, and most usefully, the actual ROWID of the row being waited on.

The query can be used on RAC as it uses the GV$ views to report blocking across all database instances rather than just the one you're connected to.

Using this Script

This is a more detailed diagnostic script than the simple "is anything blocked right now?" script. Because if self joins to GV$LOCK, uses an INTERSECT, and gets the ROWIDs, it is heavier than the lightweight blocker script found here and may hang on busy systems with lots of locks. See below.

IMPORTANT - If the instance is already under severe load, use the light weight blocker script first, which can be found linked above.

Once you've confirmed there's a genuine blocker/waiter issue, this script provides the additional detail needed to identify the session, SQL, and affected row.

Key Column Details and Explanation

WAITER_SID

Session waiting for the lock

LOCKER_SID

Session holding the lock

WAITER_SQL

SQL currently blocked

LOCKER_SQL

SQL currently holding the lock

LOCKER_PREV_SQL

Previous SQL executed by the blocker (often useful when the blocker is idle)

WAITER_ROWID_WAITING_ON

Exact row being waited on

WAITER_LOCK_TYPE

Type of lock (TX, TM, etc.)

WAITER_MODE_REQ

Lock mode requested by the waiting session

SECS_OBJECT_LOCKED

How long the blocking session has held the lock

Using the ROWID

When the wait is against a specific row, the generated ROWID can be used to identify the exact row involved in the contention. For example,

SELECT *

FROM schema.table_name

WHERE ROWID = 'AAAS5rAAEAAAACXAAA';

This is particularly valuable when investigating row level (TX) lock waits.

Typical Troubleshooting

Identify the waiting session (WAITER_SID).
Note the blocking session (LOCKER_SID).
Check the blocking session's SQL_ID and PREV_SQL_ID.
Review SECS_OBJECT_LOCKED to determine how long the lock has been held.
If a ROWID is provided, query the affected row.
Contact the application owner or terminate the blocking session if appropriate.

Best Practice

Run this script from Oracle SQL Developer, where the wide output is much easier to read. It works in SQL*Plus, but the large number of columns makes the results difficult to interpret without additional formatting.

Published on

Published 25th September 2026