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

この記事で分かること

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

-- 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検索のパターンも大文字で指定するのが基本です。ダブルクォーテーションで作成された小文字テーブル名の場合は注意が必要です。

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

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

-- 方法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;

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

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

-- _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 ' || 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;

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;

-- 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;

-- スキーマ名付きの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エラーになります。

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で切り捨てが可能ですが、全件を取得したい場合はカーソルを使う方が確実です。

-- 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_%';

-- 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

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;

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

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文の一部ではありません。

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しました

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つのテーブルでエラーが起きても残りのテーブルの処理は継続されます。本番環境での一括削除では必ずこのパターンを使いましょう。

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;
/

-- 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);

-- ステップ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

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;
/

-- 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

-- ログテーブルの作成
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)
);

-- ログ付き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しているため、問題ありません。

-- テスト用プレフィックスのテーブルを一括削除
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;
/

-- 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;
/

-- テストスキーマの全テーブルを削除（要注意）
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関数でスキーマ名をチェックし、テスト用スキーマ以外では実行できないように安全ガードを入れています。本番スキーマで誤って実行されるのを防ぐために、このようなチェックは必ず入れてください。

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

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

-- リサイクルビンの内容を確認
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$%'を追加すると、リサイクルビンのテーブルを除外できます。

ORA-01031: insufficient privileges

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

-- テーブルをロックしているセッションを確認
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_%';

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