-------------------------------------------
-- Victor Khalturin
-- dbpilot.net
--
-- plsqllocktimer_c.sql
-- Version 13:12 20.08.2020 (001)
--
-- Cluster Database
-- Find all sessions with the PL/SQL lock timer wait event
-------------------------------------------
SET LINES 300
SET PAGES 900
SET LONG 200000
COL INSTANCE_HOSTNAME FOR A25
COL DB_USER FOR A10 
COL PID FOR A5
COL SID FOR 999999
COL SERIAL# FOR 999999
COL CONNECTED_FROM FOR A25
COL CONNECTED_BY FOR A12
COL PORT FOR 9999999
COL DB_NODE FOR 999
COL OS_USER FOR A10 
COL APPLICATION FOR A30
COL SQL_ID FOR a15
COL CHILD# FOR 999999
COL SQLTEXT FOR A50 WORD_WRAPPED

SELECT I.HOST_NAME INSTANCE_HOSTNAME,
       S.INST_ID,
       S.USERNAME DB_USER,
       P.SPID PID,
       S.SID,
       S.SERIAL#,
       S.MACHINE CONNECTED_FROM,
       S.OSUSER CONNECTED_BY,
       S.PORT,
       S.MODULE APPLICATION,
       S.LAST_CALL_ET WAITING_SEC,
       S.SQL_ID,
       S.SQL_CHILD_NUMBER "CHILD#",
       SQL.EXECUTIONS,
       -- SQL.SQL_FULLTEXT SQLTEXT
       SQL.SQL_TEXT SQLTEXT
FROM 
       GV$SESSION S, 
       GV$PROCESS P, 
       GV$SQL SQL,
       GV$INSTANCE I
WHERE
       S.INST_ID=P.INST_ID AND
       S.INST_ID=SQL.INST_ID AND
       S.INST_ID=I.INST_ID AND
       S.PADDR=P.ADDR AND
       S.SQL_ID=SQL.SQL_ID AND 
       S.SQL_CHILD_NUMBER=SQL.CHILD_NUMBER AND
       S.EVENT='PL/SQL lock timer';
-------------------------------------------
-- Victor Khalturin
-- dbpilot.net
--
-- plsqllocktimer_c.sql
-- Version 13:12 20.08.2020 (001)
-------------------------------------------