*以下は、サポート契約締結中のお客さまに2019年4月に配信したサポートレターより一部抜粋して掲載しています。

SQL を途中で中断する場合、セッションの KILL や DISCONNECT、Ctrl+Alt を使用していると思います。11g 以降のバージョンではリソース・マネージャのプラン・ディレクティブを使用することで実行中のセッションを終了せずに SQL のキャンセルすることができるようになりましたが、前もって設定が必要でした。

18c 以上のバージョンでは 手動で不要な SQL 文をキャンセルすることができるようになっています。今回は "ALTER SYSTEM CANCEL SQL" 文による SQL のキャンセルについてご紹介します。

構文

ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';

確認

実際に SELECT 文と INSERT 文をキャンセルした際に、どのような出力および動作になるのか確認していきます。

1. TESTユーザーで以下一連の SQL 文をコピーし実行

セッション1

SELECT COUNT(*) FROM TEST_CANCEL A , TEST_CANCEL B , TEST_CANCEL C ,
TEST_CANCEL D , TEST_CANCEL E --(1);

SELECT COUNT(*) FROM TEST_CANCEL --(2);

INSERT INTO TEST_CANCEL SELECT A.C1 C1 FROM TEST_CANCEL A, TEST_CANCEL B,
TEST_CANCEL C, TEST_CANCEL D , TEST_CANCEL E --(3);

SELECT COUNT(*) FROM TEST_CANCEL --(4);

2. (1) の SELECT 文をキャンセル

手順1とは異なるセッション（セッション2）からキャンセルを実行します。

セッション2

-- 実行 SQL の確認
SQL> SELECT A.INST_ID, A.SID, A.SERIAL#, A.SQL_ID, B.SQL_TEXT
  2  FROM GV$SESSION A, GV$SQL B WHERE A.SQL_ID=B.SQL_ID AND USERNAME='TEST';

   INST_ID        SID    SERIAL# SQL_ID
---------- ---------- ---------- --------------------------
SQL_TEXT
--------------------------------------------------------------------------------
         1        264       7752 dbxksmhuz7ch8
SELECT COUNT(*) FROM TEST_CANCEL A , TEST_CANCEL B , TEST_CANCEL C , TEST_CANCEL
 D , TEST_CANCEL E --(1)

-- SQL のキャンセル
SQL> ALTER SYSTEM CANCEL SQL '264, 7752, @1, dbxksmhuz7ch8';

システムが変更されました。

セッション1

SQL> SELECT COUNT(*) FROM TEST_CANCEL A , TEST_CANCEL B , TEST_CANCEL C ,
  2  TEST_CANCEL D , TEST_CANCEL E --(1);
SELECT COUNT(*) FROM TEST_CANCEL A , TEST_CANCEL B , TEST_CANCEL C ,
                     *
行1でエラーが発生しました。:
ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました<★ sql="">
SQL> SELECT COUNT(*) FROM TEST_CANCEL --(2);

  COUNT(*)
----------
       101

セッションを KILL した場合、ORA-00028 が発生しますが、キャンセルの場合はORA-01013 が発生します。(1)の SQL をキャンセルしていますが、セッションは継続しているため後続の処理(2)が実行されています。

3. (3) のINSERT文のキャンセル

セッション2

-- 実行 SQL の確認
SQL> SELECT A.INST_ID, A.SID, A.SERIAL#, A.SQL_ID, B.SQL_TEXT
  2  FROM GV$SESSION A, GV$SQL B WHERE A.SQL_ID=B.SQL_ID AND USERNAME='TEST';

   INST_ID        SID    SERIAL# SQL_ID
---------- ---------- ---------- --------------------------
SQL_TEXT
--------------------------------------------------------------------------------
         1        264       7752 4twthn2v0vsdt
INSERT INTO TEST_CANCEL SELECT A.C1 C1 FROM TEST_CANCEL A, TEST_CANCEL B, TEST_C
ANCEL C, TEST_CANCEL D , TEST_CANCEL E --(3)

-- SQL のキャンセル
SQL> ALTER SYSTEM CANCEL SQL '264, 7752, @1, 4twthn2v0vsdt';

システムが変更されました。

セッション1

SQL> INSERT INTO TEST_CANCEL SELECT A.C1 C1 FROM TEST_CANCEL A, TEST_CANCEL B,
  2  TEST_CANCEL C, TEST_CANCEL D , TEST_CANCEL E --(3);
INSERT INTO TEST_CANCEL SELECT A.C1 C1 FROM TEST_CANCEL A, TEST_CANCEL B,
            *
行1でエラーが発生しました。:
ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました<★ sql="">
SQL>SELECT COUNT(*) FROM TEST_CANCEL --(4);

  COUNT(*)
----------
       101 <★>

(2)と(4)の SELECT 文の値が同じことから、(3)の INSERT 文がロールバックされてされていることがわかります。

まとめ

今回は SQL 文のキャンセルの動作について確認しました。
セッションを停止するのとほぼ同じ内容を確認することで、特定の SQL 文のみをキャンセルすることができ、後続の処理を中断する必要がありません。
特定の SQL 文で問題が発生した場合に使用されてみてはいかがでしょうか。

参考情報

• Oracle^® Database管理者ガイド 18c
  5.10.5 セッション内のSQL文の取消し
  https://docs.oracle.com/cd/E96517_01/admin/managing-processes.html#GUID-7D8E5E00-515D-4338-8B86-C2044F6D2957
• 18c で SQL 問合せを取消し（キャンセル）する方法 (Doc ID 2492642.1)

(オラクル事業部 サポート・サービス担当 加藤)

• Oracle関連サービス