Persistent Locks

-- Title:       PersistentLocks.sql
-- Description: SQL script for database preventive health checkup 
-- Date         Author              Description
-- ------------ ------------------- ------------------------------


----------------------------------------------------------------
-- Statements for terminating the persistent locking sessions --
----------------------------------------------------------------
CLEAR COLUMNS
COLUMN EVENT FORMAT A43
COLUMN SPID FORMAT A10
COLUMN KILL_STMT FORMAT A54
DEFINE LINE2='                         CRITICAL!!! Statements for terminating the persistent locking sessions                         '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT 'ALTER SYSTEM KILL SESSION '''||SES.SID||','||SES.SERIAL#||',@'|| SES.INST_ID||''' IMMEDIATE;' KILL_STMT,
PRC.SPID, ROUND(MAX(LK.CTIME)/60/60,2) "WAIT HOUR", SES.EVENT
FROM GV$LOCK LK, GV$SESSION SES, SYS.OBJ$ OBJ, GV$PROCESS PRC
WHERE SES.INST_ID = LK.INST_ID 
AND SES.SID = LK.SID 
AND OBJ.OBJ# = LK.ID1 
AND SES.INST_ID = PRC.INST_ID
AND SES.PADDR = PRC.ADDR 
AND LK.TYPE = 'TM'  
AND SES.USERNAME = &DB_SCHEMA
AND LK.CTIME > 1200
GROUP BY SES.SID, SES.SERIAL#, SES.INST_ID, PRC.SPID, SES.EVENT
ORDER BY "WAIT HOUR" DESC;

--------------------------------------------------------
-- Sessions which are blocked by the persistent locks --
--------------------------------------------------------
CLEAR COLUMNS
DEFINE LINE2='                             CRITICAL!!! Sessions which are blocked by the persistent locks                             '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT A.INST_ID, A.SID, ' IS BLOCKING ', B.INST_ID, B.SID, ' SINCE FROM ', B.CTIME/60 MIN, ' ON ', A.BLOCK, B.REQUEST, A.LMODE
FROM GV$LOCK A, GV$LOCK B
WHERE A.ID1 = B.ID1 
AND A.ID2 = B.ID2 
AND A.BLOCK > 0 
AND B.REQUEST > 0 
AND (A.INST_ID, A.SID) IN (
SELECT DISTINCT LK.INST_ID, LK.SID
FROM GV$LOCK LK, GV$SESSION SES, SYS.OBJ$ OBJ
WHERE SES.INST_ID = LK.INST_ID 
AND SES.SID = LK.SID
AND OBJ.OBJ# = LK.ID1 
AND LK.TYPE = 'TM' 
AND SES.USERNAME = &DB_SCHEMA 
AND LK.CTIME > 1200
)
ORDER BY MIN;

-------------------------------------------------------------------------
-- Sessions details of persistent locks which are more than 20 minutes --
-------------------------------------------------------------------------
CLEAR COLUMNS
COLUMN SESSION_DETAIL FORMAT A14
COLUMN USERNAME FORMAT A10
COLUMN MACHINE FORMAT A10
COLUMN PROGRAM FORMAT A25
COLUMN OSUSER FORMAT A8
COLUMN STATUS FORMAT A8
COLUMN CURRENT_SQL_ID FORMAT A25
COLUMN PREVIOUS_SQL_ID FORMAT A25
COLUMN CURRENT_SQL FORMAT A94
COLUMN PREVIOUS_SQL FORMAT A94
DEFINE LINE2='                    CRITICAL!!! Sessions details of persistent locks which are more than 20 minutes                     '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT DISTINCT SES.SID||','||SES.SERIAL#||',@'||SES.INST_ID SESSION_DETAIL, 
SES.USERNAME, 
SES.MACHINE, 
SES.PROGRAM, SES.OSUSER, SES.LOGON_TIME, 
SES.STATUS, SES.STATE, 'CURRENT  -> '||SES.SQL_ID CURRENT_SQL_ID, (
SELECT DISTINCT TRIM(SQL_TEXT) 
FROM GV$SQL 
WHERE HASH_VALUE = SES.SQL_HASH_VALUE  
AND INST_ID = SES.INST_ID
) CURRENT_SQL, 
'PREVIOUS -> '||SES.PREV_SQL_ID PREVIOUS_SQL_ID, (
SELECT DISTINCT TRIM(SQL_TEXT) 
FROM GV$SQL 
WHERE HASH_VALUE = SES.PREV_HASH_VALUE  
AND INST_ID = SES.INST_ID
) PREVIOUS_SQL
FROM GV$LOCK LK, GV$SESSION SES, SYS.OBJ$ OBJ
WHERE SES.INST_ID = LK.INST_ID 
AND SES.SID = LK.SID
AND OBJ.OBJ# = LK.ID1 
AND LK.TYPE = 'TM' 
AND SES.USERNAME = &DB_SCHEMA 
AND LK.CTIME > 1200;

-------------------------------------------------------------------------------------
-- Details of locked tables due to persistent locks which are more than 20 minutes --
-------------------------------------------------------------------------------------
CLEAR COLUMNS
COLUMN SESSION_DETAIL FORMAT A28
COLUMN TABLE_NAME FORMAT A39
COLUMN CURRENT_SQL_ID FORMAT A25
COLUMN PREVIOUS_SQL_ID FORMAT A25

DEFINE LINE2='              CRITICAL!!! Details of locked tables due to persistent locks which are more than 20 minutes               '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

BREAK ON SESSION_DETAIL ON CURRENT_SQL_ID ON PREVIOUS_SQL_ID SKIP 1

SELECT DISTINCT SES.SID||','||SES.SERIAL#||',@'||SES.INST_ID SESSION_DETAIL, SES.SQL_ID CURRENT_SQL_ID, SES.PREV_SQL_ID PREVIOUS_SQL_ID, OBJ.NAME TABLE_NAME
FROM GV$LOCK LK, GV$SESSION SES, SYS.OBJ$ OBJ
WHERE SES.INST_ID = LK.INST_ID 
AND SES.SID = LK.SID
AND OBJ.OBJ# = LK.ID1 
AND LK.TYPE = 'TM' 
AND SES.USERNAME = &DB_SCHEMA
AND LK.CTIME > 1200
ORDER BY SESSION_DETAIL;

CLEAR BREAKS

-----------------------------------------------------
-- Long operations details on the persistent locks --
-----------------------------------------------------
CLEAR COLUMNS
COLUMN SESSION_DETAIL FORMAT A25
COLUMN UNITS FORMAT A16
COLUMN OPNAME FORMAT A25
COLUMN SQL_TEXT FORMAT A94
DEFINE LINE2='                            CRITICAL!!! Current long operations details on persistent locks                             '
TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 CENTER LINE1

SELECT SES.SID||','||SES.SERIAL#||',@'||SES.INST_ID SESSION_DETAIL, 
START_TIME, LAST_UPDATE_TIME, TOTALWORK, SOFAR, UNITS,
ELAPSED_SECONDS "SOFAR_SEC", TIME_REMAINING "REMAIN_SEC", OPNAME, (
SELECT DISTINCT TRIM(SQL_TEXT) 
FROM GV$SQL 
WHERE HASH_VALUE = SES.SQL_HASH_VALUE 
AND INST_ID = SES.INST_ID
) SQL_TEXT
FROM GV$SESSION_LONGOPS SES
WHERE INST_ID||':'||SID||':'||SERIAL# IN (
SELECT DISTINCT SES.INST_ID||':'|| SES.SID||':'|| SES.SERIAL#
FROM GV$LOCK LK, GV$SESSION SES, SYS.OBJ$ OBJ
WHERE SES.INST_ID = LK.INST_ID 
AND SES.SID = LK.SID
AND OBJ.OBJ# = LK.ID1 
AND LK.TYPE = 'TM' 
AND SES.USERNAME = &DB_SCHEMA 
AND LK.CTIME > 1200
)
AND SOFAR < TOTALWORK;