------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 15:10 18-Sep-2022 (005)
--
-- The DBT_BLOCKERS.sql displays the blocking session for each blocked session.
-- Each row represents a blocking and a blocked session pair.
------------------------------------------------------------

SET PAGES 30
SET LINES 200
SET SQLNUMBER OFF
SET VERIFY OFF
COL BLOCKING_SESSION FOR A40 
COL BLOCKED_SESSION FOR A40
COL LOCKED_OBJECT FOR A40
COL LOCKED_ROWID FOR A20
COL LOCKED_SQLID FOR A15

UNDEFINE SHOW_MACHINE;
SELECT /*+ RULE */
       SES1.SID || ',' || SES1.SERIAL# || '(' || NVL(SES1.USERNAME,'BACKGROUND') || ') ' || NVL2('&&SHOW_MACHINE',REGEXP_REPLACE(REGEXP_REPLACE(SES1.MACHINE,'\w+\\',''),'\..+$',''),NULL) BLOCKING_SESSION,
       SES2.SID || ',' || SES2.SERIAL# || '(' || NVL(SES2.USERNAME,'BACKGROUND') || ') ' || NVL2('&&SHOW_MACHINE',REGEXP_REPLACE(REGEXP_REPLACE(SES2.MACHINE,'\w+\\',''),'\..+$',''),NULL) BLOCKED_SESSION, 
       SES2.SECONDS_IN_WAIT SEC_IN_WAIT,
       ROUND(SES2.SECONDS_IN_WAIT/60) MIN_IN_WAIT,
       DO.OWNER || '.' || DO.OBJECT_NAME LOCKED_OBJECT,
       CASE WHEN SES2.ROW_WAIT_OBJ#=-1 
            THEN 'N/A'
            ELSE DBMS_ROWID.ROWID_CREATE(1, 
            SES2.ROW_WAIT_OBJ#, SES2.ROW_WAIT_FILE#,
            SES2.ROW_WAIT_BLOCK#, SES2.ROW_WAIT_ROW#)
       END AS LOCKED_ROWID,
       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;