查看阻塞语句oracle

SELECT LEVEL AS LEVEL_NUM,
B.SID AS "会话ID",
B.SESS_SERIAL# AS "会话序列",
B.BLOCKER_INSTANCE_ID AS "阻塞实列ID",
B.BLOCKER_SID AS "阻塞会话ID",
B.BLOCKER_SESS_SERIAL# AS "阻塞会话序列",
SS.EVENT AS "阻塞者等待事件",
S.SCHEMANAME AS "模式名",
S.OSUSER AS "操作系统用户",
S.PROCESS AS "进程ID",
S.MACHINE AS "机器",
S.PORT AS "端口",
S.TERMINAL AS "终端",
S.PROGRAM AS "程序",
S.TYPE AS "类型",
S.SQL_ID AS "当前SQLID",
(SELECT TXT.SQL_TEXT FROM V$SQLAREA TXT WHERE TXT.SQL_ID = S.SQL_ID) AS "当前SQL语句",
S.PREV_SQL_ID AS "上个SQLID",
(SELECT TXT.SQL_TEXT FROM V$SQLAREA TXT WHERE TXT.SQL_ID = S.PREV_SQL_ID) AS "上个SQL语句",
B.WAIT_EVENT_TEXT AS "等待事件",
S.WAIT_CLASS AS "等待类",
S.WAIT_TIME AS "等待时间秒",
'||' AS "阻塞者信息分隔",
SS.STATUS AS "阻塞者会话ID",
SS.STATE AS "阻塞者会话ID",
SS.SCHEMANAME AS "阻塞者模式名",
SS.OSUSER AS "阻塞者操作系统用户",
SS.PROCESS AS "阻塞者进程ID",
SS.MACHINE AS "阻塞者机器",
SS.PORT AS "阻塞者端口",
SS.TERMINAL AS "阻塞者终端",
SS.PROGRAM AS "阻塞者程序",
SS.TYPE AS "阻塞者类型",
SS.SQL_ID AS "阻塞者当前SQLID",
(SELECT TXT.SQL_TEXT FROM V$SQLAREA TXT WHERE TXT.SQL_ID = SS.SQL_ID) AS "当前SQL语句",
SS.PREV_SQL_ID AS "上个SQLID",
(SELECT TXT.SQL_TEXT FROM V$SQLAREA TXT WHERE TXT.SQL_ID = SS.PREV_SQL_ID) AS "上个SQL语句",
SS.WAIT_CLASS AS "等待类",
SS.WAIT_TIME AS "等待时间秒",
SS.EVENT AS "等待事件",
SS.P1TEXT AS "参数一名",
             SS.P1 AS "参数一值",
SS.P2TEXT AS "参数二名",
SS.P2 AS "参数二值",
SS.P3TEXT AS "参数三名",
SS.P3 AS "参数三值",
'alter system kill session ''' || TO_CHAR(SS.SID) || ',' || TO_CHAR(SS.SERIAL#) || ''';' KILLSESSION,
'kill -9 ' || P.SPID AS KILLSYSPROC
FROM V$SESSION_BLOCKERS B
LEFT JOIN V$SESSION S ON S.SID = B.SID AND S.SERIAL# = B.SESS_SERIAL#
LEFT JOIN V$SESSION SS ON SS.SID = B.BLOCKER_SID AND SS.SERIAL# = B.BLOCKER_SESS_SERIAL#
LEFT JOIN V$PROCESS P ON P.ADDR = SS.PADDR
    START WITH SS.WAIT_CLASS='Idle'
CONNECT BY PRIOR B.SID=B.BLOCKER_SID