Print Friendly, PDF & Email
A query to identify blocked and blocking sessions in an Oracle Database.
DEMO

DEMO: HOW TO IDENTIFY BLOCKED AND BLOCKING SESSIONS IN ORACLE DATABASE?

RELATED

DEMO: ORA-00060: DEADLOCK DETECTED WHILE WAITING FOR RESOURCE

QUERY

The DBT_BLOCKERS view displays the blocking session for each blocked session. Each row represents a blocking and a blocked session pair.

------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 21:57 30-Oct-2021 (001)
--
-- DBT_BLOCKERS - DATABASE TOOLS, BLOCKERS
--
-- The DBT_BLOCKERS view displays the blocking session for each blocked session.
-- Each row represents a blocking and a blocked session pair.
--
------------------------------------------------------------

-- CREATE OR REPLACE VIEW DBT_BLOCKERS AS 
SELECT SES1.SID || ',' || SES1.SERIAL# || '(' || NVL(SES1.USERNAME,'BACKGROUND') || ')' "BLOCKING",
       SES2.SID || ',' || SES2.SERIAL# || '(' || NVL(SES2.USERNAME,'BACKGROUND') || ')' "BLOCKED", 
	   SES2.SECONDS_IN_WAIT,
	   ROUND(SES2.SECONDS_IN_WAIT/60) MINUTES_IN_WAIT,
	   DO.OWNER || '.' || DO.OBJECT_NAME "ON_OBJECT",
	   DBMS_ROWID.ROWID_CREATE(1, SES2.ROW_WAIT_OBJ#, SES2.ROW_WAIT_FILE#, 
	   SES2.ROW_WAIT_BLOCK#, SES2.ROW_WAIT_ROW#) "ROWID_FOR_LOCKED_ROW",
	   SES2.SQL_ID "LOCKED_SQLID"
FROM   V$SESSION SES1, V$SESSION SES2, DBA_OBJECTS DO 
WHERE  SES1.SID=SES2.BLOCKING_SESSION AND SES2.ROW_WAIT_OBJ#=DO.OBJECT_ID AND 
       SES2.BLOCKING_SESSION IS NOT NULL
ORDER BY 1,3;

EXAMPLE

SET PAGES 100
SET LINES 200 
COL BLOCKING FOR A20 
COL BLOCKED FOR A20
COL ON_OBJECT FOR A40
COL ROWID_FOR_LOCKED_ROW FOR A20
COL LOCKED_SQLID FOR A15

SELECT * FROM DBT_BLOCKERS;
BLOCKING             BLOCKED              SECONDS_IN_WAIT MINUTES_IN_WAIT ON_OBJECT       ROWID_FOR_LOCKED_ROW LOCKED_SQLID
-------------------- -------------------- --------------- --------------- --------------- -------------------- ---------------
17,1816(DEMO)        55,35751(DEMO)                  4903              82 DEMO.B_ROW      AAASfmAAHAAAAFuAAA   5zprqh5ydng78

Written At
31 OCT 202112:40
OEL Release
7.9 x64
Database Release
19.12.0.0.0