実行中セッションKILLコマンド取得

いきさつ

何故か終了しないセッションを終了するためのコマンドを取得します。システム運用していれば必ず必要になりますよね。

取得SQL

------------------------------------------------------------------
-- Copyright(c) 2015 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2015.08.23 作成
-- 2015.08.27 排他項目にOWNERを追加,開始時刻を経過時間に変更
-- 
-- [項目]
-- ROW_NO         : 行番号
-- SID            : セッション識別子
-- SERIAL#        : セッション・シリアル番号
-- MACHINE        : 接続マシン名
-- PORT           : 接続ポート番号
-- PROGRAM        : 接続プログラム
-- STATUS         : 接続状態
-- COURSE_TIME    : 経過時間
-- SCAN_INFO      : 読み込み情報
-- BLOCK_OBJECT   : ブロックしているオブジェクト情報(複数ある場合どれか１つ)
-- WAIT_OBJECT    : ロック開放待ちのオブジェクト情報
-- SQL_ID         : Oracleが管理しているSQLID
-- SQL_TEXT       : SQLコマンド構文
-- OPTIMIZER_PLAN : コストおよび実行計画
-- KILL_CMDLINE   : KILLコマンド
------------------------------------------------------------------
SELECT ROW_NUMBER() OVER (ORDER BY S.SQL_EXEC_START, S.SID, V_1.SUB_NO) AS ROW_NO
     , DECODE(V_1.SUB_NO,0,S.SID)     AS SID
     , DECODE(V_1.SUB_NO,0,S.SERIAL#) AS SERIAL#
     , DECODE(V_1.SUB_NO,0,S.MACHINE) AS MACHINE
     , DECODE(V_1.SUB_NO,0,S.PORT)    AS PORT
     , DECODE(V_1.SUB_NO,0,S.PROGRAM) AS PROGRAM
     , DECODE(V_1.SUB_NO,0,S.STATUS)  AS STATUS
     , DECODE(V_1.SUB_NO,0,TO_CHAR(TRUNC(SYSDATE)+(SYSDATE - S.SQL_EXEC_START),'HH24:MI:SS')) AS COURSE_TIME
     , CASE WHEN V_1.SUB_NO=0 THEN
       (SELECT TO_CHAR(L.SOFAR * 100 / L.TOTALWORK , 'FM00.00') || '% (' || L.OPNAME || ':' || L.TARGET || ')'
          FROM V$SESSION_LONGOPS L
         WHERE L.SID     = S.SID
           AND L.SERIAL# = S.SERIAL#
           AND L.SQL_ID  = S.SQL_ID
           AND L.SOFAR  <> L.TOTALWORK
           AND ROWNUM    <  2)
       END AS SCAN_INFO
     , CASE WHEN V_1.SUB_NO=0 THEN
       (SELECT OB.OWNER || '.' || OB.OBJECT_NAME || '(FILE=' || DF.FILE_NAME || ';BLOCK=' || SY.ROW_WAIT_BLOCK# || ';WAIT_SESSION=' || SY.SID || ')'
          FROM V$SESSION       SY
             , DBA_OBJECTS     OB
             , DBA_DATA_FILES  DF
         WHERE S.SID           = SY.BLOCKING_SESSION
           AND OB.OBJECT_ID    = SY.ROW_WAIT_OBJ#
           AND DF.FILE_ID      = SY.ROW_WAIT_FILE#
           AND ROWNUM < 2 )
       END AS BLOCK_OBJECT
     , CASE WHEN V_1.SUB_NO=0 THEN
       (SELECT OB.OWNER || '.' || OB.OBJECT_NAME || '(FILE=' || DF.FILE_NAME || ';BLOCK=' || S.ROW_WAIT_BLOCK# || ';LOCKED_SESSION=' || SY.SID || ')'
          FROM V$SESSION       SY
             , DBA_OBJECTS     OB
             , DBA_DATA_FILES  DF
         WHERE SY.SID          = S.BLOCKING_SESSION
           AND OB.OBJECT_ID    = S.ROW_WAIT_OBJ#
           AND DF.FILE_ID      = S.ROW_WAIT_FILE#
           AND ROWNUM < 2 )
       END AS WAIT_OBJECT
     , DECODE(V_1.SUB_NO,0,S.SQL_ID) AS SQL_ID
     , V_1.SQL_TEXT
     , V_1.OPTIMIZER_PLAN
     , CASE WHEN V_1.SUB_NO=0 AND S.TYPE <> 'BACKGROUND' THEN
            'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''' IMMEDIATE;'
       END AS KILL_CMDLINE
  FROM (
SELECT DISTINCT --これがないと、うまくFULL OUTER JOINしない
       NVL( V_P.SQL_ID, V_T.SQL_ID ) AS SQL_ID
     , NVL( V_P.CHILDN, V_T.CHILDN ) AS CHILDN
     , NVL( V_P.SUB_NO, V_T.SUB_NO ) AS SUB_NO
     , V_T.SQL_TEXT
     , REPLACE(REPLACE(V_P.OPTIMIZER_PLAN, ' {Access:}',NULL),' {Filter:}',NULL) AS OPTIMIZER_PLAN
  FROM ( SELECT V_S.SQL_ID
              , V_S.SQL_CHILD_NUMBER AS CHILDN
              , T.PIECE AS SUB_NO
              , T.SQL_TEXT
           FROM ( SELECT DISTINCT S.SQL_ID, S.SQL_CHILD_NUMBER FROM V$SESSION S WHERE S.SQL_ID IS NOT NULL ) V_S
              , V$SQLTEXT T
          WHERE V_S.SQL_ID = T.SQL_ID
     ) V_T FULL OUTER JOIN (
         SELECT V_S.SQL_ID
              , V_S.SQL_CHILD_NUMBER AS CHILDN
              , P.ID AS SUB_NO
              , NVL(TO_CHAR(P.COST,'999,999,999,999'),'                ') || ' | ' ||
                CASE WHEN P.ID=0 THEN P.OPERATION || ' (Optimizer=' || P.OPTIMIZER || ')'
                     ELSE LPAD( ' ', P.DEPTH * 2, ' ' ) || P.OPERATION || RTRIM(' ' || P.OPTIONS) || RTRIM(' ' || P.OBJECT_NAME)
                           || ' {Access:' || P.ACCESS_PREDICATES || '} {Filter:' || P.FILTER_PREDICATES || '}'
                END AS OPTIMIZER_PLAN
           FROM ( SELECT DISTINCT S.SQL_ID, S.SQL_CHILD_NUMBER FROM V$SESSION S WHERE S.SQL_ID IS NOT NULL ) V_S
              , SYS.V_$SQL_PLAN_STATISTICS_ALL P
          WHERE V_S.SQL_ID           = P.SQL_ID
            AND V_S.SQL_CHILD_NUMBER = P.CHILD_NUMBER
     ) V_P
  ON ( V_T.SQL_ID  = V_P.SQL_ID
   AND V_T.CHILDN  = V_P.CHILDN
   AND V_T.SUB_NO  = V_P.SUB_NO )
     ) V_1
     , V$SESSION S
 WHERE S.SQL_ID          IS NOT NULL
   AND S.SQL_ID           = V_1.SQL_ID
   AND S.SQL_CHILD_NUMBER = V_1.CHILDN
   AND S.SID <> (select distinct my.SID from v$mystat my )
 UNION ALL
SELECT 999 AS ROW_NO
     , S.SID
     , S.SERIAL#
     , S.MACHINE
     , S.PORT
     , S.PROGRAM
     , S.STATUS
     , NULL AS COURSE_TIME
     , NULL AS SCAN_INFO
     , (SELECT OB.OWNER || '.' || OB.OBJECT_NAME || '(FILE=' || DF.FILE_NAME || ';BLOCK=' || SY.ROW_WAIT_BLOCK# || ';WAIT_SESSION=' || SY.SID || ')'
          FROM V$SESSION       SY
             , DBA_OBJECTS     OB
             , DBA_DATA_FILES  DF
         WHERE S.SID           = SY.BLOCKING_SESSION
           AND OB.OBJECT_ID    = SY.ROW_WAIT_OBJ#
           AND DF.FILE_ID      = SY.ROW_WAIT_FILE#
           AND ROWNUM < 2 ) AS BLOCK_OBJECT
     , NULL AS WAIT_OBJECT
     , NULL AS SQL_ID
     , NULL AS SQL_TEXT
     , NULL AS OPTIMIZER_PLAN
     , CASE WHEN S.TYPE <> 'BACKGROUND' THEN
            'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''' IMMEDIATE;'
       END AS KILL_CMDLINE
  FROM V$SESSION S
 WHERE S.SQL_ID  IS NULL
   AND S.SID <> (select distinct my.SID from v$mystat my)
   AND (SELECT COUNT(*) FROM V$SESSION SY WHERE SY.BLOCKING_SESSION = S.SID) > 0
 ORDER BY
       ROW_NO ASC
;