oracle 日付テーブル削除

Last updated at 2024-09-08Posted at 2024-08-30

以下は、Oracle 19c SE2環境で特定の条件に基づいてテーブルを削除するプロセス全体のスクリプトです。すべての操作をログに記録し、削除するテーブルを選択するためにCSVファイルを生成し、最終的には新しく作成したオブジェクトやテーブルをクリーンアップする手順が含まれています。

概要

ディレクトリオブジェクトの作成: ログファイルやCSVファイルの保存場所を設定します。
対象テーブルの選定とCSV出力: 特定のパターンに一致するテーブルを選び、ログとともにCSVファイルに出力します。
CSVファイルの確認と修正: 不要なテーブルをCSVから手動で除外します。
CSVファイルをOracleテーブルにロード: sqlldrを使用してCSVデータを新しいテーブルにロードします。
テーブルの削除: 新しいテーブルに基づいてテーブルを削除します。
後処理: 使用した一時テーブルとディレクトリオブジェクトを削除します。
ログの記録: 全プロセスを通じてログファイルに記録します。

Step 1: ディレクトリオブジェクトの作成

最初に、UTL_FILEを使用してファイル操作を行うために、ディレクトリオブジェクトを作成します。

CREATE DIRECTORY MY_DIRECTORY AS 'C:\path\to\your\local\directory';
GRANT READ, WRITE ON DIRECTORY MY_DIRECTORY TO your_username;

Step 2: テーブル名を基に対象テーブルをCSVに出力

次に、特定のパターン（YYYYMMDD, YYMMDD, YYMMを含む）で名前が付けられたテーブルを選び、ログとともにCSVファイルに出力します。

SPOOL C:\path\to\your\local\directory\tables_to_delete.csv

SELECT 
    'OWNER', 
    'TABLE_NAME', 
    'BYTES'
FROM 
    dual
UNION ALL
SELECT 
    OWNER, 
    TABLE_NAME, 
    BYTES 
FROM 
    DBA_SEGMENTS 
WHERE 
    SEGMENT_TYPE = 'TABLE' 
    AND REGEXP_LIKE(TABLE_NAME, '.*[0-9]{8}.*|.*[0-9]{6}.*|.*[0-9]{4}.*')
    AND OWNER NOT IN ('SYS', 'SYSTEM')
ORDER BY 
    OWNER, TABLE_NAME;

SPOOL OFF

このSQLは、DBA_SEGMENTSビューを使用して、特定のパターンに一致するテーブルを選択し、指定されたディレクトリにCSVファイルとして出力します。出力には列名も含まれます。

Step 3: CSVファイルを手動で確認

生成されたtables_to_delete.csvファイルを開き、削除したくないテーブルがあれば手動で削除します。

Step 4: CSVファイルをOracleテーブルにロード

手動で修正したCSVファイルをOracleの一時テーブルにロードします。

コントロールファイル (`control_file.ctl`)

LOAD DATA
INFILE 'C:\path\to\your\local\directory\tables_to_delete.csv'
INTO TABLE TEMP_TABLES_TO_DELETE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
    OWNER CHAR(30),
    TABLE_NAME CHAR(30),
    BYTES INTEGER EXTERNAL
)

`sqlldr` コマンド

sqlldr your_username/your_password@your_sid control=control_file.ctl log=upload.log

Step 5: テーブル削除プロセスの実行

ロードされたテーブルに基づいて、対象のテーブルを削除し、プロセスをログに記録します。

DECLARE
    v_log_file UTL_FILE.FILE_TYPE;
BEGIN
    -- ログファイルを開く
    v_log_file := UTL_FILE.FOPEN('MY_DIRECTORY', 'process.log', 'W');
    UTL_FILE.PUT_LINE(v_log_file, '=== テーブル削除プロセス開始: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ' ===');
    UTL_FILE.FCLOSE(v_log_file);

    -- 再度ログファイルを開いてプロセス実行
    v_log_file := UTL_FILE.FOPEN('MY_DIRECTORY', 'process.log', 'A');

    -- 1. TEMP_TABLES_TO_DELETE テーブルの作成
    UTL_FILE.PUT_LINE(v_log_file, 'テーブル TEMP_TABLES_TO_DELETE を作成中...');
    EXECUTE IMMEDIATE 'CREATE TABLE TEMP_TABLES_TO_DELETE (OWNER VARCHAR2(30), TABLE_NAME VARCHAR2(30), BYTES NUMBER)';
    UTL_FILE.PUT_LINE(v_log_file, 'テーブル TEMP_TABLES_TO_DELETE を作成完了。');

    -- 2. sqlldrでCSVファイルをアップロード
    UTL_FILE.PUT_LINE(v_log_file, 'CSVファイルを TEMP_TABLES_TO_DELETE にアップロード中...');
    -- csvファイルのアップロードをコマンドラインで実行後
    UTL_FILE.PUT_LINE(v_log_file, 'CSVファイルのアップロード完了。');

    -- 3. TEMP_TABLES_TO_DELETEの内容に基づいてテーブルを削除
    UTL_FILE.PUT_LINE(v_log_file, 'テーブル削除プロセスを開始...');
    FOR rec IN (SELECT owner, table_name FROM TEMP_TABLES_TO_DELETE) LOOP
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE ' || rec.owner || '.' || rec.table_name;
            UTL_FILE.PUT_LINE(v_log_file, '削除されたテーブル: ' || rec.owner || '.' || rec.table_name);
        EXCEPTION
            WHEN OTHERS THEN
                UTL_FILE.PUT_LINE(v_log_file, 'エラーが発生しました: ' || SQLERRM);
        END;
    END LOOP;
    UTL_FILE.PUT_LINE(v_log_file, 'テーブル削除処理が完了しました。');

    -- 4. TEMP_TABLES_TO_DELETE テーブルの削除
    UTL_FILE.PUT_LINE(v_log_file, 'テーブル TEMP_TABLES_TO_DELETE を削除中...');
    EXECUTE IMMEDIATE 'DROP TABLE TEMP_TABLES_TO_DELETE PURGE';
    UTL_FILE.PUT_LINE(v_log_file, 'テーブル TEMP_TABLES_TO_DELETE を削除完了。');

    -- 5. ディレクトリオブジェクトの削除
    UTL_FILE.PUT_LINE(v_log_file, 'ディレクトリオブジェクトを削除中...');
    EXECUTE IMMEDIATE 'DROP DIRECTORY MY_DIRECTORY';
    UTL_FILE.PUT_LINE(v_log_file, 'ディレクトリオブジェクトの削除完了。');

    -- ログファイルを閉じる
    UTL_FILE.PUT_LINE(v_log_file, '=== テーブル削除プロセス終了: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ' ===');
    UTL_FILE.FCLOSE(v_log_file);
END;
/

注意事項

ディレクトリオブジェクトとパス: CREATE DIRECTORYで指定するパスは、Oracleサーバーがアクセス可能なパスにしてください。
SQL*Loaderの使用: sqlldrコマンドを実行するためには、Oracleクライアントがインストールされている必要があります。
ログファイル: ログファイルの内容を確認し、エラーがないかチェックすることをお勧めします。

結論

このスクリプトは、特定の命名規則に従うテーブルを効率的に削除するための強力なツールです。プロセス全体がログに記録され、必要に応じて手動でテーブルの削除を制御できるため、安全かつ効率的にデータ管理を行うことができます。プロセスの最後にディレクトリオブジェクトも削除するため、再実行時にエラーを回避できます。

もしテーブル名の前半に正しい日付（YYYYMMDD、YYMMDD、YYMM）があり、後半に干渉する数字や文字列がある場合は、正規表現を調整して、テーブル名の先頭にある日付を検出し、後続の干渉する部分を無視するようにすることができます。

対応するSQLスクリプト

前半に日付があり、後半に干渉項がある場合でも正しく日付を抽出するために、次のような正規表現を使います。

SPOOL C:\path\to\your\local\directory\tables_to_delete.csv

SELECT 
    'OWNER', 
    'TABLE_NAME', 
    'BYTES'
FROM 
    dual
UNION ALL
SELECT 
    OWNER, 
    TABLE_NAME, 
    BYTES 
FROM 
    DBA_SEGMENTS 
WHERE 
    SEGMENT_TYPE = 'TABLE' 
    -- 前半に日付形式（YYMMDD または YYYYMMDD）があり、後半に干渉項があるパターン
    AND (
        -- YYYYMMDD 形式の日付を検出し、後ろに干渉項があってもOK
        REGEXP_LIKE(TABLE_NAME, '^[0-9]{8}[^0-9].*')
        -- YYMMDD 形式の日付を検出し、後ろに干渉項があってもOK
        OR REGEXP_LIKE(TABLE_NAME, '^[0-9]{6}[^0-9].*')
        -- YYMM 形式の日付を検出し、後ろに干渉項があってもOK
        OR REGEXP_LIKE(TABLE_NAME, '^[0-9]{4}[^0-9].*')
    )
    -- システムのテーブルを除外
    AND OWNER NOT IN ('SYS', 'SYSTEM')
ORDER BY 
    OWNER, TABLE_NAME;

SPOOL OFF;

正規表現の分解

^[0-9]{8}[^0-9].*:
- 意味: テーブル名の先頭に8桁の数字（YYYYMMDD）があり、その後に数字以外の任意の文字が続くパターン。
- 例: 20230801_REPORT_12345 のように、テーブル名の最初に日付があり、その後にアルファベットや他の文字が続く場合にマッチします。
^[0-9]{6}[^0-9].*:
- 意味: テーブル名の先頭に6桁の数字（YYMMDD）があり、その後に数字以外の任意の文字が続くパターン。
- 例: 230815_REPORT_12345 のように、テーブル名の最初に6桁の日付があり、その後に他の文字が続く場合にマッチします。
^[0-9]{4}[^0-9].*:
- 意味: テーブル名の先頭に4桁の数字（YYMM）があり、その後に数字以外の任意の文字が続くパターン。
- 例: 2308_REPORT_12345 のように、テーブル名の最初に4桁の日付があり、その後に他の文字が続く場合にマッチします。
[^0-9]:
- 意味: 数字以外の任意の文字を意味します。これにより、日付の後に何らかの文字列が続くパターンにマッチさせることができます。

その他の条件

システムアカウントの除外:
- AND OWNER NOT IN ('SYS', 'SYSTEM')
- システムアカウントに属するテーブルは対象外にすることで、Oracleシステムによって自動的に作成されたテーブルを無視します。

例

正しい日付を含むテーブル名:
- 20230801_REPORT12345: 最初の20230801が日付として認識され、後ろの干渉項は無視されます。
- 230815_DATA_98765: 最初の230815が日付として認識され、後ろの干渉項は無視されます。
- 2308_TEST_TABLE99999: 最初の2308が日付として認識され、後ろの干渉項は無視されます。
干渉項を無視する例:
- REPORT20230801_12345: テーブル名の前にREPORTがあり、干渉項として判断されるため、このテーブルは無視されます。
- DATA12345678_230815: 最初に8桁の干渉項があり、その後に日付があっても、前半が干渉項として判断されます。

まとめ

この正規表現を使うことで、テーブル名の前半に日付が含まれている場合や後半に干渉する数字や文字列が含まれている場合でも、正しい日付を抽出して処理することが可能になります。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up