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
HOW TO FIND ALL SESSIONS HOLDING A TABLE LOCK

QUERY

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

EXAMPLE

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