Oracle查看长时间持有锁并阻塞其他会话session，生成处理语句

Oracle 查看长时间持有锁并阻塞其他会话session，生成处理语句

提供给程序开发、程序运维人员使用

-- 提供运维人员使用，查看长时间持有锁并阻塞其他会话session，生成处理语句
SELECT '锁源session: '||A_S.SID||','||A_S.SERIAL#||',@'||A_S.INST_ID||', '||A_S.SCHEMANAME||', From: '||A_S.OSUSER||','||A_S.MACHINE||', '||A_S.PROGRAM||', Cur_SQL: '||A_S.SQL_ID||',Prev_SQL: '|| A_S.PREV_SQL_ID||', Status:'||A_S.STATUS||', Lock_Time: '||A.CTIME||'s.'||CHR(10)||
        ' -> 等待锁session:'||B.SID||','||B_S.INST_ID||','||B_S.MACHINE||','||B_S.EVENT||', Blocked_SQL: '||B_S.SQL_ID||', Locked_ON: '||OBJ.OWNER||'.'||OBJ.OBJECT_NAME||', Lock_Mode: '||A.TYPE||','||
       DECODE(A.LMODE, 0, '0,none', 1, '1,NULL', 2, '2,row-S(SS)', 3, '3,row-X(SX)', 4, '4,share(S)', 5, '5,S/Row-X(SSX)', 6, '6,exclusive(X)')||CHR(10)||
       ' --> 查询锁定数据SQL:  '||(DECODE(OBJ.OBJECT_TYPE, 'TABLE', 'SELECT * FROM '||OBJ.OWNER||'.'||OBJ.OBJECT_NAME||' WHERE ROWID = '''||
               DBMS_ROWID.ROWID_CREATE(1, OBJ.DATA_OBJECT_ID, B_S.ROW_WAIT_FILE#, B_S.ROW_WAIT_BLOCK#, B_S.ROW_WAIT_ROW#)||''';', NULL))||CHR(10)||
       ' --> 查询锁源操作SQL:  SELECT DISTINCT ASH.SESSION_ID, ASH.SESSION_SERIAL#, ASH.MACHINE, ASH.PROGRAM, ASH.XID, ASH.SAMPLE_TIME, ASH.SQL_ID , ASH.SQL_OPNAME, ASH.SQL_EXEC_START, ASH.EVENT, Q.SQL_TEXT FROM GV$ACTIVE_SESSION_HISTORY ASH JOIN GV$SQL Q ON ASH.INST_ID=q.INST_ID AND ASH.SQL_ID = q.SQL_ID and ASH.SQL_PLAN_HASH_VALUE=q.PLAN_HASH_VALUE WHERE ASH.SESSION_ID = '|| A_S.SID ||' AND ASH.SESSION_SERIAL# = '|| A_S.SERIAL# ||' AND ASH.INST_ID = '|| A_S.INST_ID ||' ORDER BY 1, 2, 3, 4, 5, 6;'||CHR(10)||
       ' --> 查询相关SQL文本:  SELECT DISTINCT SQL_ID,SQL_TEXT FROM GV$SQL WHERE SQL_ID IN ('''||A_S.SQL_ID||''','''||A_S.PREV_SQL_ID||''','''||B_S.SQL_ID||''');'||CHR(10)||
       ' --> 清理session命令:  alter system disconnect session '''||A_S.SID||','||A_S.SERIAL#||',@'||A_S.INST_ID||''' immediate;' AS BLOCK_DETAIL
  FROM GV$LOCK     A,
       GV$LOCK     B,
       GV$SESSION  A_S,
       GV$SESSION  B_S,
       DBA_OBJECTS OBJ,
       DBA_USERS U
 WHERE A.ID1 = B.ID1
   AND A.ID2 = B.ID2
   AND A_S.USER#=U.USER_ID
   AND U.ORACLE_MAINTAINED='N' /* 排除Oracle内部账户持有的锁 */
   AND A.CTIME >= 30 /* 持有锁时间超过 30s */
   AND A.BLOCK > 0 /* 阻塞了其他的会话 */
   AND B.REQUEST > 0
   AND A_S.TYPE='USER'
   AND A.SID = A_S.SID
   AND A.INST_ID = A_S.INST_ID
   AND B.SID = B_S.SID
   AND B.INST_ID = B_S.INST_ID
   AND A_S.SID = B_S.FINAL_BLOCKING_SESSION
   AND A_S.INST_ID = B_S.FINAL_BLOCKING_INSTANCE
   AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+)
 ORDER BY A.INST_ID, A.SID;