強制的にプロセスをKILL(ロック解除)を行うSQL

こじ

7年前

SQLを実行したけど、結果が返ってこなくて、ロックがかかってしまうことは良くあると思います。

そういった場合に使える、強制的にプロセスをKILLするサンプルSQLになります。

SQLのプロセスをKILL(ロック解除)する方法

プロセスがロックされている状態

SQL> DROP TABLE TB_SAMPLE;

行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

ロックされているSQLの情報を抽出する

V$SESSION、V$SQL、V$LOCKを使用してロックされているSQLの情報を抽出できます。

SQLのSESSIONNをKILLするためにはSIDとSERIALが必須になります。

また、SQL文も抽出しないと、KILLしたいSIDとSERIALがどれなのかを見つけることができないので、SQL文を抽出するのも必須かと思います。

SELECT
    X.SID
  , X.SERIAL#
  , TO_CHAR(
        X.SQL_EXEC_START
      , 'YYYY/MM/DD HH24:MI:SS'
    ) AS SQL_EXEC_START
  , Y.SQL_TEXT
FROM
    V$SESSION X
        INNER JOIN V$SQL Y
           ON Y.HASH_VALUE = X.SQL_HASH_VALUE
          AND Y.ADDRESS    = X.SQL_ADDRESS
WHERE
    X.STATUS = 'ACTIVE'
AND X.SID IN (
        SELECT
            Z.SID
        FROM
            V$LOCK Z
        WHERE
            Z.TYPE IN ('TM','TX')
    )
;

 SID  SERIAL# SQL_EXEC_START       SQL_TEXT
---- -------- -------------------- ----------------------------------------------------------------------------------------------------------
  98     3349 2018/04/05 01:31:19  INSERT INTO TB_SAMPLE (ID,NAME,MEMO1,MEMO2,MEMO3,MEMO4,MEMO5) VALUES(:B7 ,:B6 ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 )

SID,SERIAL#を使ってKILL(ロック解除)

ALTER SYSTEM KILL SESSION '98, 3349'
システムが変更されました。

ロックされているプロセスが無いことを確認

SELECT
    X.SID
  , X.SERIAL#
  , TO_CHAR(
        X.SQL_EXEC_START
      , 'YYYY/MM/DD HH24:MI:SS'
    ) AS SQL_EXEC_START
  , Y.SQL_TEXT
FROM
    V$SESSION X
        INNER JOIN V$SQL Y
           ON Y.HASH_VALUE = X.SQL_HASH_VALUE
          AND Y.ADDRESS    = X.SQL_ADDRESS
WHERE
    X.STATUS = 'ACTIVE'
AND X.SID IN (
        SELECT
            Z.SID
        FROM
            V$LOCK Z
        WHERE
            Z.TYPE IN ('TM','TX')
    )
;

レコードが選択されませんでした。

ロック解除したため操作可能

SQL> DROP TABLE TB_SAMPLE;

表が削除されました。

以上です。