------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 12:08 08-May-2022 (001)
--
-- DBT_ROW_LOCKS - DATABASE TOOLS, ROW LOCKS
--
-- The DBT_ROW_LOCKS view displays all sessions holding the lock on a table.
-- LOCKED_ROWID shows locked ROWID only if the session is currently waiting for another transaction to commit.
------------------------------------------------------------

SET LINES 300 
SET PAGES 999
COL USERNAME FOR A10
COL SID FOR 999999
COL PID FOR A6
COL SERIAL# FOR 999999
COL STATUS FOR A15
COL CONNECTED_FROM FOR A20
COL PROGRAM_NAME FOR A20
COL OBJECT_NAME FOR A11
COL LOCKED_MODE FOR 9999

SELECT NVL(SES.USERNAME,'BACKGROUND') USERNAME,
       SES.SID,
       SES.SERIAL#,
       PRC.SPID PID,
       CASE WHEN SES.STATUS = 'ACTIVE' AND SES.BLOCKING_SESSION IS NOT NULL 
            THEN 'ACTIVE AND BLOCKED' 
            ELSE SES.STATUS 
       END AS STATUS,
       NVL2(SES.BLOCKING_SESSION,'YES','NO') BLOCKED,
       SES.BLOCKING_SESSION BLOCKED_BY,
       SES.LAST_CALL_ET STATUS_ELAP_SEC,
       SES.MACHINE CONNECTED_FROM,
       SUBSTR(SES.PROGRAM,1,20) PROGRAM_NAME,
       OBJ.OWNER || '.' || OBJ.OBJECT_NAME OBJECT_NAME,
       CASE WHEN SES.ROW_WAIT_OBJ#=-1 
            THEN NULL
            ELSE DBMS_ROWID.ROWID_CREATE(1, 
            SES.ROW_WAIT_OBJ#, SES.ROW_WAIT_FILE#,
            SES.ROW_WAIT_BLOCK#, SES.ROW_WAIT_ROW#)
       END AS LOCKED_ROWID,
       LCK.LOCKED_MODE,
       DECODE(LCK.LOCKED_MODE,
       0,'NONE: lock requested but not yet obtained',
       1,'NULL',
       2,'ROWS_S (SS): Row Share Lock',
       3,'ROW_X (SX): Row Exclusive Table Lock',
       4,'SHARE (S): Share Table Lock',
       5,'S/ROW-X (SSX): Share Row Exclusive Table Lock',
       6,'Exclusive (X): Exclusive Table Lock'       
       ) LOCKED_MODE_DESC
FROM
       V$SESSION SES,
       V$PROCESS PRC,
       DBA_OBJECTS OBJ,
       V$LOCKED_OBJECT LCK
WHERE
       LCK.SESSION_ID=SES.SID AND
       LCK.OBJECT_ID=OBJ.OBJECT_ID AND
       SES.PADDR=PRC.ADDR;