【Oracle】テーブル名をLIKE検索してDROP TABLE文を一括生成する方法｜PL/SQL動的実行も解説

Oracleデータベースで「テスト用に作ったテーブルをまとめて消したい」「バックアップテーブルが大量に残っているので一括削除したい」という場面は、開発や運用の現場で頻繁に発生します。しかし、テーブルを1つずつDROP TABLEするのは非効率で、対象が数十〜数百に及ぶと現実的ではありません。

この記事では、USER_TABLESやALL_TABLESをLIKE検索してテーブルを絞り込み、DROP TABLE文を動的に生成・実行する方法を解説します。SELECT文での生成からPL/SQLでの自動実行、安全に運用するためのパターンまで、実務で使えるテクニックを網羅します。

この記事で分かること

USER_TABLES / ALL_TABLES をLIKE検索でフィルタする方法
SELECT文でDROP TABLE文を動的生成する方法（|| 演算子）
LISTAGG / カーソルで一括生成するテクニック
PL/SQLのEXECUTE IMMEDIATEで動的に実行する方法
dry-run・ログ記録・バックアップなど安全な運用パターン
実務でよく使うシナリオとよくあるミス・注意点

USER_TABLES / ALL_TABLESをLIKE検索でフィルタする
SELECT文でDROP TABLE文を動的生成する
LISTAGG / カーソルで一括生成する
PL/SQLで動的に実行する方法
安全な運用パターン
実務でよく使うシナリオ
よくあるミスと注意点
まとめ

USER_TABLES / ALL_TABLESをLIKE検索でフィルタする

DROP TABLE文を一括生成するには、まず対象テーブルを特定する必要があります。Oracleではデータディクショナリビューを使って、テーブル名をパターンで検索できます。

USER_TABLESで自スキーマのテーブルを検索する

USER_TABLESは、現在のユーザーが所有するテーブルの一覧を返すデータディクショナリビューです。WHERE句にLIKEを指定することで、テーブル名のパターンマッチングができます。

SQL

-- TMP_ で始まるテーブルを検索
SELECT table_name
FROM   user_tables
WHERE  table_name LIKE 'TMP_%'
ORDER BY table_name;

実行結果

TABLE_NAME
------------------------------
TMP_CUSTOMERS
TMP_ORDERS
TMP_PRODUCTS
TMP_SALES_202401
TMP_SALES_202402

ポイント：Oracleのデータディクショナリでは、テーブル名はデフォルトで大文字で格納されます。そのため、LIKE検索のパターンも大文字で指定するのが基本です。ダブルクォーテーションで作成された小文字テーブル名の場合は注意が必要です。

ALL_TABLESで他スキーマも含めて検索する

他のユーザーが所有するテーブルも含めて検索する場合は、ALL_TABLESを使います。OWNER列でスキーマを絞り込むことで、より正確な検索ができます。

SQL

-- 特定スキーマの TMP_ テーブルを検索
SELECT owner, table_name
FROM   all_tables
WHERE  owner      = 'HR'
AND    table_name LIKE 'TMP_%'
ORDER BY table_name;

UPPER() / LOWER() で大文字小文字を吸収する

ダブルクォーテーション付きで作成されたテーブルは、小文字や混在した名前で格納されることがあります。このような場合、UPPER()関数を使えば大文字小文字を気にせず検索できます。

SQL

-- 大文字・小文字を問わず検索
SELECT table_name
FROM   user_tables
WHERE  UPPER(table_name) LIKE 'TMP_%'
ORDER BY table_name;

複数パターンで検索する（LIKE + OR / REGEXP_LIKE）

複数のプレフィックスに一致するテーブルを検索する場合、LIKEをORで繋げるか、REGEXP_LIKEで正規表現を使います。

SQL

-- 方法1: LIKE + OR で複数パターンを指定
SELECT table_name
FROM   user_tables
WHERE  table_name LIKE 'TMP_%'
OR     table_name LIKE 'TEST_%'
OR     table_name LIKE 'BK_%'
ORDER BY table_name;

SQL

-- 方法2: REGEXP_LIKE で正規表現を使用
SELECT table_name
FROM   user_tables
WHERE  REGEXP_LIKE(table_name, '^(TMP|TEST|BK)_')
ORDER BY table_name;

方法	書き方	適するケース
LIKE + OR	パターンごとにLIKEを記述	パターンが2〜3個の場合
REGEXP_LIKE	正規表現で1行にまとめる	パターンが多い場合・複雑な条件

注意：REGEXP_LIKEはOracle 10g以降で使用できます。それ以前のバージョンではLIKE + ORを使ってください。また、正規表現はLIKEに比べてパフォーマンスが劣る場合があるため、大量のテーブルが存在する環境では注意が必要です。

後方一致・部分一致の検索パターン

プレフィックスだけでなく、サフィックスや部分一致でも検索できます。

SQL

-- _BK で終わるテーブル（バックアップ用テーブル）
SELECT table_name
FROM   user_tables
WHERE  table_name LIKE '%_BK';

-- 日付文字列を含むテーブル（例: ORDERS_20240101）
SELECT table_name
FROM   user_tables
WHERE  REGEXP_LIKE(table_name, '_[0-9]{8}$');

-- テーブル名に LOG を含むテーブル
SELECT table_name
FROM   user_tables
WHERE  table_name LIKE '%LOG%';

SELECT文でDROP TABLE文を動的生成する

対象テーブルが特定できたら、次はそのテーブル名を使ってDROP TABLE文を自動的に組み立てます。Oracleの文字列連結演算子||を使えば、SELECT結果としてDROP文を生成できます。

基本: || 演算子で文字列連結する

最もシンプルなパターンです。'DROP TABLE 'という文字列とテーブル名を||で連結し、実行可能なSQL文を生成します。

SQL

SELECT 'DROP TABLE ' || table_name || ';' AS drop_stmt
FROM   user_tables
WHERE  table_name LIKE 'TMP_%'
ORDER BY table_name;

実行結果

DROP_STMT
------------------------------------------
DROP TABLE TMP_CUSTOMERS;
DROP TABLE TMP_ORDERS;
DROP TABLE TMP_PRODUCTS;
DROP TABLE TMP_SALES_202401;
DROP TABLE TMP_SALES_202402;

この結果をコピーしてSQL*Plusやツールに貼り付ければ、一括でDROPを実行できます。

CASCADE CONSTRAINTSオプションを付ける

テーブルに外部キー制約が設定されている場合、通常のDROP TABLEはエラーになります。CASCADE CONSTRAINTSを付けると、関連する外部キー制約を自動で削除してからテーブルをDROPできます。

SQL

SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;' AS drop_stmt
FROM   user_tables
WHERE  table_name LIKE 'TMP_%'
ORDER BY table_name;

実行結果

DROP_STMT
--------------------------------------------------
DROP TABLE TMP_CUSTOMERS CASCADE CONSTRAINTS;
DROP TABLE TMP_ORDERS CASCADE CONSTRAINTS;
DROP TABLE TMP_PRODUCTS CASCADE CONSTRAINTS;

PURGEオプションでゴミ箱を回避する

Oracle 10g以降では、DROP TABLEを実行するとテーブルは完全に削除されず、リサイクルビン（ゴミ箱）に移動します。リサイクルビンに移動されたテーブルはBIN$...という名前で残り続けます。

PURGEオプションを付けると、リサイクルビンを経由せず完全に削除されます。テスト用テーブルなど復元の必要がないテーブルには、PURGEを付けるのが一般的です。

SQL

-- CASCADE CONSTRAINTS + PURGE の組み合わせ
SELECT 'DROP TABLE ' || table_name
       || ' CASCADE CONSTRAINTS PURGE;' AS drop_stmt
FROM   user_tables
WHERE  table_name LIKE 'TMP_%'
ORDER BY table_name;

オプション	効果	使うべき場面
なし	リサイクルビンに移動（復元可能）	念のため復元できるようにしたい場合
CASCADE CONSTRAINTS	参照している外部キー制約も削除	他テーブルから参照されている場合
PURGE	リサイクルビンを経由せず完全削除	テスト用・一時テーブルなど復元不要な場合
CASCADE CONSTRAINTS PURGE	両方の効果	テスト環境の一括クリーンアップ

他スキーマのテーブルを含むDROP文を生成する

他のスキーマが所有するテーブルをDROPする場合は、テーブル名の前にスキーマ名（OWNER）を付ける必要があります。

SQL

-- スキーマ名付きのDROP TABLE文を生成
SELECT 'DROP TABLE ' || owner || '.' || table_name
       || ' CASCADE CONSTRAINTS PURGE;' AS drop_stmt
FROM   all_tables
WHERE  owner IN ('HR', 'SCOTT')
AND    table_name LIKE 'TMP_%'
ORDER BY owner, table_name;

実行結果

DROP_STMT
----------------------------------------------------
DROP TABLE HR.TMP_EMPLOYEES CASCADE CONSTRAINTS PURGE;
DROP TABLE HR.TMP_DEPARTMENTS CASCADE CONSTRAINTS PURGE;
DROP TABLE SCOTT.TMP_BONUS CASCADE CONSTRAINTS PURGE;

ポイント：他スキーマのテーブルをDROPするには、DROP ANY TABLEシステム権限が必要です。権限がない場合はORA-01031: insufficient privilegesエラーになります。

LISTAGG / カーソルで一括生成する

前のセクションでは1行ずつDROP文が生成されましたが、LISTAGG関数を使えば複数のDROP文を1つの文字列にまとめられます。また、カーソルFORループを使えばSPOOLファイルに出力して一括実行するスクリプトを作成できます。

LISTAGGで改行区切りの一括出力

LISTAGG関数は複数行の値を1つの文字列に連結します。区切り文字に改行（CHR(10)）を指定すれば、実行可能なスクリプトを一括で生成できます。

SQL

SELECT LISTAGG(
         'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS PURGE;',
         CHR(10)
       ) WITHIN GROUP (ORDER BY table_name) AS drop_script
FROM   user_tables
WHERE  table_name LIKE 'TMP_%';

実行結果

DROP_SCRIPT
--------------------------------------------------
DROP TABLE TMP_CUSTOMERS CASCADE CONSTRAINTS PURGE;
DROP TABLE TMP_ORDERS CASCADE CONSTRAINTS PURGE;
DROP TABLE TMP_PRODUCTS CASCADE CONSTRAINTS PURGE;
DROP TABLE TMP_SALES_202401 CASCADE CONSTRAINTS PURGE;
DROP TABLE TMP_SALES_202402 CASCADE CONSTRAINTS PURGE;

注意：LISTAGGの結果は最大4000バイト（VARCHAR2の上限）です。対象テーブルが多い場合はORA-01489: result of string concatenation is too longエラーになります。Oracle 12c R2以降ではLISTAGG ... ON OVERFLOW TRUNCATEで切り捨てが可能ですが、全件を取得したい場合はカーソルを使う方が確実です。

Oracle 12c R2以降: ON OVERFLOW対応

Oracle 12c R2（12.2）以降では、LISTAGGにオーバーフロー制御を追加できます。

SQL

-- 12c R2以降: 4000バイトを超えたら切り捨て
SELECT LISTAGG(
         'DROP TABLE ' || table_name || ' PURGE;',
         CHR(10)
         ON OVERFLOW TRUNCATE '...'
       ) WITHIN GROUP (ORDER BY table_name) AS drop_script
FROM   user_tables
WHERE  table_name LIKE 'TMP_%';

カーソルFORループでSPOOL出力する

テーブル数が多い場合や、SQL*Plusからファイルに出力したい場合は、カーソルFORループとDBMS_OUTPUTを組み合わせます。SQL*PlusのSPOOLコマンドで出力先をファイルに指定すれば、実行用スクリプトを自動生成できます。

SQL*Plus

-- SQL*PlusでSPOOL出力するスクリプト
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SPOOL /tmp/drop_tmp_tables.sql

BEGIN
  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    WHERE  table_name LIKE 'TMP_%'
    ORDER BY table_name
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(
      'DROP TABLE ' || rec.table_name
      || ' CASCADE CONSTRAINTS PURGE;'
    );
  END LOOP;
END;
/

SPOOL OFF

このスクリプトを実行すると、/tmp/drop_tmp_tables.sqlに以下のようなファイルが生成されます。

生成されたファイル（drop_tmp_tables.sql）

DROP TABLE TMP_CUSTOMERS CASCADE CONSTRAINTS PURGE;
DROP TABLE TMP_ORDERS CASCADE CONSTRAINTS PURGE;
DROP TABLE TMP_PRODUCTS CASCADE CONSTRAINTS PURGE;
DROP TABLE TMP_SALES_202401 CASCADE CONSTRAINTS PURGE;
DROP TABLE TMP_SALES_202402 CASCADE CONSTRAINTS PURGE;

生成されたファイルは@/tmp/drop_tmp_tables.sqlで実行できます。

SPOOL方式のメリット：生成されたスクリプトを実行前に目視確認できるため、意図しないテーブルが含まれていないかチェックできます。本番環境では、この「生成→確認→実行」の2ステップ方式が推奨されます。

PL/SQLで動的に実行する方法

ここまでの方法はDROP文を「生成」するだけでしたが、PL/SQLのEXECUTE IMMEDIATEを使えば、生成と実行を1ステップで自動化できます。テスト環境のクリーンアップや定期的なメンテナンスタスクに便利です。

基本: EXECUTE IMMEDIATEで即時実行

EXECUTE IMMEDIATEは、文字列として渡されたSQL文を動的に実行するPL/SQLの構文です。カーソルFORループと組み合わせることで、検索結果のすべてのテーブルを順にDROPできます。

PL/SQL

BEGIN
  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    WHERE  table_name LIKE 'TMP_%'
  ) LOOP
    EXECUTE IMMEDIATE
      'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';
  END LOOP;
END;
/

注意：EXECUTE IMMEDIATEに渡すSQL文の末尾にセミコロン（;）を付けてはいけません。セミコロンを付けるとORA-00911: invalid characterエラーになります。セミコロンはSQL*Plusの区切り文字であり、SQL文の一部ではありません。

削除件数を表示しながら実行する

どのテーブルがDROPされたかを確認するために、DBMS_OUTPUTで処理状況を出力するパターンです。

PL/SQL

SET SERVEROUTPUT ON

DECLARE
  v_count  NUMBER := 0;
  v_total  NUMBER := 0;
BEGIN
  -- 対象件数を事前カウント
  SELECT COUNT(*) INTO v_total
  FROM   user_tables
  WHERE  table_name LIKE 'TMP_%';

  DBMS_OUTPUT.PUT_LINE('対象テーブル数: ' || v_total);
  DBMS_OUTPUT.PUT_LINE('----------------------------');

  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    WHERE  table_name LIKE 'TMP_%'
    ORDER BY table_name
  ) LOOP
    EXECUTE IMMEDIATE
      'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';
    v_count := v_count + 1;
    DBMS_OUTPUT.PUT_LINE(
      v_count || '/' || v_total || ' DROP完了: ' || rec.table_name
    );
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('----------------------------');
  DBMS_OUTPUT.PUT_LINE('完了: ' || v_count || 'テーブルをDROPしました');
END;
/

実行結果

対象テーブル数: 5
----------------------------
1/5 DROP完了: TMP_CUSTOMERS
2/5 DROP完了: TMP_ORDERS
3/5 DROP完了: TMP_PRODUCTS
4/5 DROP完了: TMP_SALES_202401
5/5 DROP完了: TMP_SALES_202402
----------------------------
完了: 5テーブルをDROPしました

例外処理で安全に実行する

複数テーブルを一括でDROPする際、途中でエラーが発生すると残りのテーブルが処理されません。EXCEPTIONブロックでエラーをキャッチして処理を続行するパターンが実務では重要です。

PL/SQL

SET SERVEROUTPUT ON

DECLARE
  v_success  NUMBER := 0;
  v_fail     NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    WHERE  table_name LIKE 'TMP_%'
    ORDER BY table_name
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';
      v_success := v_success + 1;
      DBMS_OUTPUT.PUT_LINE('[OK]  ' || rec.table_name);
    EXCEPTION
      WHEN OTHERS THEN
        v_fail := v_fail + 1;
        DBMS_OUTPUT.PUT_LINE(
          '[NG]  ' || rec.table_name
          || ' - ' || SQLERRM
        );
    END;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('==============================');
  DBMS_OUTPUT.PUT_LINE('成功: ' || v_success || ' / 失敗: ' || v_fail);
END;
/

実行結果（一部のテーブルがロックされていた場合）

[OK]  TMP_CUSTOMERS
[OK]  TMP_ORDERS
[NG]  TMP_PRODUCTS - ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
[OK]  TMP_SALES_202401
[OK]  TMP_SALES_202402
==============================
成功: 4 / 失敗: 1

ポイント：内側のBEGIN...EXCEPTION...ENDブロックをループの中に入れることで、1つのテーブルでエラーが起きても残りのテーブルの処理は継続されます。本番環境での一括削除では必ずこのパターンを使いましょう。

引数で検索パターンを渡すプロシージャ

汎用的に使えるよう、検索パターンを引数で受け取るストアドプロシージャにまとめることもできます。

PL/SQL

CREATE OR REPLACE PROCEDURE drop_tables_like (
  p_pattern  IN  VARCHAR2,
  p_purge    IN  BOOLEAN DEFAULT TRUE,
  p_dry_run  IN  BOOLEAN DEFAULT FALSE
) AS
  v_sql      VARCHAR2(500);
  v_success  NUMBER := 0;
  v_fail     NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    WHERE  table_name LIKE p_pattern
    ORDER BY table_name
  ) LOOP
    v_sql := 'DROP TABLE ' || rec.table_name
             || ' CASCADE CONSTRAINTS';
    IF p_purge THEN
      v_sql := v_sql || ' PURGE';
    END IF;

    IF p_dry_run THEN
      DBMS_OUTPUT.PUT_LINE('[DRY-RUN] ' || v_sql);
    ELSE
      BEGIN
        EXECUTE IMMEDIATE v_sql;
        v_success := v_success + 1;
        DBMS_OUTPUT.PUT_LINE('[OK]  ' || rec.table_name);
      EXCEPTION
        WHEN OTHERS THEN
          v_fail := v_fail + 1;
          DBMS_OUTPUT.PUT_LINE('[NG]  ' || rec.table_name || ' - ' || SQLERRM);
      END;
    END IF;
  END LOOP;

  IF NOT p_dry_run THEN
    DBMS_OUTPUT.PUT_LINE('成功: ' || v_success || ' / 失敗: ' || v_fail);
  END IF;
END;
/

このプロシージャの使い方は以下のとおりです。

PL/SQL

-- dry-run: 削除対象を確認するだけ（実行しない）
EXEC drop_tables_like('TMP_%', p_dry_run => TRUE);

-- 本実行: テーブルをDROP
EXEC drop_tables_like('TMP_%');

-- PURGEなし（リサイクルビンに残す）
EXEC drop_tables_like('TEST_%', p_purge => FALSE);

安全な運用パターン

DROP TABLEはDDL文であるため、実行した瞬間に暗黙的COMMITが発生し、ロールバックできません。本番環境で一括削除を行う際は、以下の安全策を講じることが重要です。

dry-run（確認用SELECT）→ 実行の2ステップ

最も基本的な安全策は、まず対象テーブルの一覧を確認し、問題なければ実行するという2ステップ方式です。

SQL

-- ステップ1: 対象テーブルを確認（dry-run）
SELECT table_name,
       num_rows,
       last_analyzed
FROM   user_tables
WHERE  table_name LIKE 'TMP_%'
ORDER BY table_name;

実行結果

TABLE_NAME            NUM_ROWS  LAST_ANALYZED
--------------------  --------  -------------------
TMP_CUSTOMERS              150  2024-01-15 10:30:00
TMP_ORDERS                 500  2024-01-15 10:30:00
TMP_PRODUCTS                80  2024-01-15 10:30:00
TMP_SALES_202401         12000  2024-02-01 09:00:00
TMP_SALES_202402          8500  2024-03-01 09:00:00

NUM_ROWSとLAST_ANALYZEDを確認することで、「まだ使われているテーブルではないか」「想定外の大きなテーブルが含まれていないか」をチェックできます。問題なければステップ2に進みます。

バックアップ（CREATE TABLE AS SELECT）してからDROP

万が一に備えて、DROPする前にテーブルのバックアップコピーを作成するパターンです。CREATE TABLE AS SELECT（CTAS）でデータごとコピーしてからDROPすることで、誤って削除した場合でもデータを復元できます。

PL/SQL

SET SERVEROUTPUT ON

DECLARE
  v_backup_name  VARCHAR2(128);
  v_date_suffix  VARCHAR2(8) := TO_CHAR(SYSDATE, 'YYYYMMDD');
BEGIN
  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    WHERE  table_name LIKE 'TMP_%'
    ORDER BY table_name
  ) LOOP
    BEGIN
      -- バックアップテーブル名を生成
      v_backup_name := 'BK_' || rec.table_name || '_' || v_date_suffix;

      -- CTAS でバックアップを作成
      EXECUTE IMMEDIATE
        'CREATE TABLE ' || v_backup_name
        || ' AS SELECT * FROM ' || rec.table_name;
      DBMS_OUTPUT.PUT_LINE('[BK]  ' || v_backup_name || ' 作成完了');

      -- 元テーブルをDROP
      EXECUTE IMMEDIATE
        'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';
      DBMS_OUTPUT.PUT_LINE('[DROP] ' || rec.table_name || ' 削除完了');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('[NG]  ' || rec.table_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

外部キー依存の確認（USER_CONSTRAINTS）

DROPしようとしているテーブルが、他のテーブルから外部キーで参照されている場合があります。事前に依存関係を確認しておくことで、意図しない影響を防げます。

SQL

-- DROP対象テーブルを参照している外部キーを確認
SELECT c.table_name       AS referencing_table,
       c.constraint_name  AS fk_name,
       r.table_name       AS referenced_table
FROM   user_constraints c
JOIN   user_constraints r
  ON   c.r_constraint_name = r.constraint_name
WHERE  c.constraint_type = 'R'
AND    r.table_name LIKE 'TMP_%'
ORDER BY r.table_name, c.table_name;

実行結果

REFERENCING_TABLE     FK_NAME              REFERENCED_TABLE
--------------------  -------------------  --------------------
TMP_ORDER_DETAILS     FK_ORD_DTL_ORDERS    TMP_ORDERS
TMP_ORDER_DETAILS     FK_ORD_DTL_PRODUCTS  TMP_PRODUCTS

この結果から、TMP_ORDERSとTMP_PRODUCTSはTMP_ORDER_DETAILSから参照されていることが分かります。CASCADE CONSTRAINTSを付けてDROPするか、先にTMP_ORDER_DETAILSをDROPする必要があります。

ログテーブルへの記録

いつ・誰が・どのテーブルを削除したかを記録するログテーブルを用意しておくと、問題が発生した際の調査に役立ちます。

SQL

-- ログテーブルの作成
CREATE TABLE drop_table_log (
  log_id        NUMBER GENERATED ALWAYS AS IDENTITY,
  table_name    VARCHAR2(128),
  dropped_by    VARCHAR2(128),
  dropped_at    TIMESTAMP DEFAULT SYSTIMESTAMP,
  status        VARCHAR2(10),
  error_message VARCHAR2(4000)
);

PL/SQL

-- ログ付きDROPプロシージャ
CREATE OR REPLACE PROCEDURE drop_tables_with_log (
  p_pattern IN VARCHAR2
) AS
BEGIN
  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    WHERE  table_name LIKE p_pattern
    AND    table_name != 'DROP_TABLE_LOG'
    ORDER BY table_name
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';

      -- 成功ログを記録
      INSERT INTO drop_table_log (table_name, dropped_by, status)
      VALUES (rec.table_name, USER, 'SUCCESS');
    EXCEPTION
      WHEN OTHERS THEN
        -- 失敗ログを記録
        INSERT INTO drop_table_log (table_name, dropped_by, status, error_message)
        VALUES (rec.table_name, USER, 'FAIL', SQLERRM);
    END;
  END LOOP;
  COMMIT;
END;
/

ポイント：DROP TABLEはDDLなので暗黙的COMMITが発生し、INSERTしたログもCOMMITされます。ただし、DDLの前に未COMMITのDMLがある場合は、DDLの暗黙COMMITで一緒にCOMMITされるため、ログテーブルへのINSERT自体はDROP前に実行しないよう注意してください。上記のプロシージャではDROP成功後にINSERTしているため、問題ありません。

実務でよく使うシナリオ

ここでは、実際の開発・運用現場でLIKE検索によるDROP TABLE一括削除が活躍するシナリオを紹介します。そのまま使えるSQLテンプレートも掲載しています。

テスト用テーブルの一括削除（TMP_, TEST_, BK_*）

開発中に作成したテスト用テーブルが溜まっていくのは、よくある問題です。プレフィックスごとにまとめて削除する例を紹介します。

PL/SQL

-- テスト用プレフィックスのテーブルを一括削除
DECLARE
  TYPE t_patterns IS TABLE OF VARCHAR2(30);
  v_patterns t_patterns := t_patterns('TMP_%', 'TEST_%', 'BK_%', 'WK_%');
  v_count    NUMBER := 0;
BEGIN
  FOR i IN 1 .. v_patterns.COUNT LOOP
    FOR rec IN (
      SELECT table_name
      FROM   user_tables
      WHERE  table_name LIKE v_patterns(i)
    ) LOOP
      BEGIN
        EXECUTE IMMEDIATE
          'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('[OK] ' || rec.table_name);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('[NG] ' || rec.table_name || ' - ' || SQLERRM);
      END;
    END LOOP;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('合計 ' || v_count || ' テーブルを削除しました');
END;
/

日付付きバックアップテーブルの古いもの削除

バックアップを取る際にテーブル名_YYYYMMDDという命名規則を使っている場合、古い日付のテーブルだけを削除するパターンです。

PL/SQL

-- 30日以上前のバックアップテーブルを削除
DECLARE
  v_cutoff  VARCHAR2(8) := TO_CHAR(SYSDATE - 30, 'YYYYMMDD');
  v_suffix  VARCHAR2(8);
  v_count   NUMBER := 0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('基準日: ' || v_cutoff || ' より古いテーブルを削除');

  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    WHERE  REGEXP_LIKE(table_name, '_[0-9]{8}$')
    ORDER BY table_name
  ) LOOP
    -- テーブル名末尾の8桁を抽出
    v_suffix := SUBSTR(rec.table_name, LENGTH(rec.table_name) - 7);

    IF v_suffix < v_cutoff THEN
      BEGIN
        EXECUTE IMMEDIATE
          'DROP TABLE ' || rec.table_name || ' PURGE';
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE('[OK] ' || rec.table_name);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('[NG] ' || rec.table_name || ' - ' || SQLERRM);
      END;
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('合計 ' || v_count || ' テーブルを削除しました');
END;
/

特定スキーマのテーブル全削除

テスト用スキーマを丸ごとクリーンアップする場合は、LIKE条件なしで全テーブルを対象にします。非常に危険な操作なので、必ずdry-runで確認してから実行してください。

PL/SQL

-- テストスキーマの全テーブルを削除（要注意）
DECLARE
  v_count NUMBER := 0;
BEGIN
  -- 安全確認: 現在のスキーマがテスト用であることを確認
  IF USER NOT IN ('TEST_SCHEMA', 'DEV_SCHEMA') THEN
    RAISE_APPLICATION_ERROR(-20001,
      'このプロシージャは TEST_SCHEMA / DEV_SCHEMA でのみ実行できます。現在: ' || USER);
  END IF;

  FOR rec IN (
    SELECT table_name
    FROM   user_tables
    ORDER BY table_name
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS PURGE';
      v_count := v_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('[NG] ' || rec.table_name || ' - ' || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('合計 ' || v_count || ' テーブルを削除しました');
END;
/

注意：全テーブル削除は取り消しができません。上記のコードではUSER関数でスキーマ名をチェックし、テスト用スキーマ以外では実行できないように安全ガードを入れています。本番スキーマで誤って実行されるのを防ぐために、このようなチェックは必ず入れてください。

よくあるミスと注意点

LIKE検索でのDROP TABLE一括実行は非常に強力ですが、それだけにミスの影響も大きくなります。ここでは、実務で特に注意すべきポイントをまとめます。

DROP TABLEは即COMMIT（ロールバック不可）

DROP TABLEはDDL（データ定義言語）であり、実行した瞬間に暗黙的COMMITが発生します。DML（INSERT/UPDATE/DELETE）のようにROLLBACKで元に戻すことはできません。

SQL

-- これは効果がない！
DROP TABLE important_data PURGE;
ROLLBACK;  -- DROPは既にCOMMIT済み。ROLLBACKしても戻らない

重要：さらに注意すべき点として、DDL実行前にCOMMITされていないDML（INSERT/UPDATE/DELETE）がある場合、DDLの暗黙COMMITによってそのDMLも一緒にCOMMITされます。DROP TABLE実行前に、未COMMITのトランザクションがないか確認してください。

リサイクルビン（BIN$…）の存在

PURGEを付けずにDROP TABLEを実行すると、テーブルはリサイクルビンに移動し、BIN$...という名前で残ります。LIKE検索の対象から意図せず外れる（または含まれる）ことがあります。

SQL

-- リサイクルビンの内容を確認
SELECT object_name,
       original_name,
       type,
       droptime
FROM   user_recyclebin
ORDER BY droptime DESC;

-- リサイクルビンから復元する場合
FLASHBACK TABLE important_table TO BEFORE DROP;

-- リサイクルビンを空にする場合
PURGE RECYCLEBIN;

ポイント：USER_TABLESにはBIN$で始まるリサイクルビンのテーブルも表示される場合があります。LIKE検索時にAND table_name NOT LIKE 'BIN$%'を追加すると、リサイクルビンのテーブルを除外できます。

権限不足（DROP ANY TABLE）

他のスキーマのテーブルをDROPするにはDROP ANY TABLE権限が必要です。権限がない状態で実行すると、以下のエラーが発生します。

エラー例

ORA-01031: insufficient privileges

操作	必要な権限
自分のテーブルをDROP	テーブルの所有者であれば権限不要
他スキーマのテーブルをDROP	`DROP ANY TABLE`システム権限
権限の確認	`SELECT * FROM user_sys_privs WHERE privilege LIKE '%DROP%'`

他セッションのロック

他のセッションがテーブルを使用中（SELECT中やDML実行中）の場合、DROP TABLEは排他ロックを取得できず失敗します。

エラー例

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ロック状況を確認するには、以下のSQLを使います。

SQL

-- テーブルをロックしているセッションを確認
SELECT s.sid,
       s.serial#,
       s.username,
       o.object_name,
       l.locked_mode
FROM   v$locked_object l
JOIN   dba_objects o
  ON   l.object_id = o.object_id
JOIN   v$session s
  ON   l.session_id = s.sid
WHERE  o.object_name LIKE 'TMP_%';

LIKE検索のワイルドカード誤りに注意

LIKE検索のワイルドカード（%と_）の指定ミスは、意図しないテーブルを削除してしまう最大の原因です。

パターン	意味	危険度
`'TMP_%'`	TMP_ で始まるテーブル	低（意図通り）
`'TMP%'`	TMP で始まる全テーブル（TMPFILE等も含む）	高
`'%TMP%'`	TMP を含む全テーブル	非常に高
`'%'`	全テーブル	致命的

必ず守るべきルール：PL/SQLで動的にDROPを実行する前に、必ず同じ条件のSELECT文で対象テーブルを確認してください。'TMP_%'と'TMP%'は結果が大きく異なる可能性があります。

まとめ

Oracleでテーブル名をLIKE検索してDROP TABLE文を一括生成する方法について解説しました。最後に、パターン別の早見表を掲載します。

やりたいこと	方法	安全性
DROP文の一覧を確認したい	SELECT + \|\| 連結	最も安全（実行しない）
スクリプトファイルを生成したい	SPOOL + カーソルFORループ	安全（目視確認可能）
1つの文字列にまとめたい	LISTAGG + CHR(10)	安全（4000バイト制限あり）
自動で即時実行したい	EXECUTE IMMEDIATE	要注意（例外処理必須）
安全に本番で実行したい	dry-run → バックアップ → 実行	推奨パターン
汎用的に使い回したい	ストアドプロシージャ化	安全（dry-runモード付き）

実務では、以下の手順を守ることで安全に一括削除を実行できます。

SELECT文で対象を確認（dry-run）: LIKE条件に一致するテーブルが意図通りかチェック
依存関係を確認: USER_CONSTRAINTSで外部キー参照がないか確認
バックアップを取得: CREATE TABLE AS SELECTでデータをコピー
例外処理付きで実行: BEGIN…EXCEPTION…ENDでエラーをキャッチ
結果をログに記録: 成功/失敗をログテーブルに保存

DROP TABLEはロールバックできないDDLです。「生成→確認→実行」の2ステップを徹底し、本番環境では必ずバックアップを取ってから実行してください。