【Oracle】DBMS_REDEFINITION完全ガイド｜オンラインテーブル再定義でゼロダウンタイムのテーブル再構成まで解説

-- CAN_REDEF_TABLE でエラーが発生しなければ再定義可能
BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE(
        uname        => 'HR',           -- テーブル所有者スキーマ
        tname        => 'EMPLOYEES',    -- 再定義対象テーブル名
        options_flag => DBMS_REDEFINITION.CONS_USE_ROWID  -- ROWID ベースの同期を使用
        -- CONS_USE_PK: 主キーベース（推奨）
        -- CONS_USE_ROWID: 主キーなしの場合
    );
    DBMS_OUTPUT.PUT_LINE('再定義可能です');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('再定義不可: ' || SQLERRM);
END;
/
-- エラーが出る主なケース:
-- ・テーブルがマテリアライズドビューのベーステーブルである
-- ・一時テーブル（Global Temporary Table）
-- ・SYS/SYSTEM スキーマのオブジェクト

-- 再定義後の構造・圧縮・表領域を指定して空の暫定テーブルを作成する
CREATE TABLE hr.employees_interim
    COMPRESS FOR OLTP              -- 圧縮を追加
    TABLESPACE users_new           -- 別の表領域へ移動
AS
    SELECT * FROM hr.employees WHERE 1=0;   -- 構造のみコピー（データなし）
-- ※ 列の変更や追加もここで行える（型変換は要注意）

-- 再定義を開始する（マテリアライズドビューログが自動作成される）
BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE(
        uname        => 'HR',
        orig_table   => 'EMPLOYEES',           -- 元のテーブル
        int_table    => 'EMPLOYEES_INTERIM',   -- 暫定テーブル
        col_mapping  => NULL,                   -- NULL = 同名列を自動マッピング
        options_flag => DBMS_REDEFINITION.CONS_USE_PK  -- 主キーで同期
    );
END;
/
-- この後、バックグラウンドでデータコピーが開始される
-- 元テーブルへの DML は通常通り実行できる（ロックなし）

-- COPY_TABLE_DEPENDENTS でインデックス・制約・トリガー・統計を自動コピーする
DECLARE
    v_errors NUMBER;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
        uname            => 'HR',
        orig_table       => 'EMPLOYEES',
        int_table        => 'EMPLOYEES_INTERIM',
        copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,  -- インデックスをコピー
        copy_triggers    => TRUE,    -- トリガーをコピー
        copy_constraints => TRUE,    -- 制約をコピー
        copy_privileges  => TRUE,    -- 権限をコピー
        ignore_errors    => FALSE,   -- エラーを無視しない
        num_errors       => v_errors
    );
    IF v_errors > 0 THEN
        DBMS_OUTPUT.PUT_LINE('コピー中にエラーが ' || v_errors || ' 件発生しました');
    END IF;
END;
/

-- 最終切り替えの直前に差分を事前同期する（切り替え時間を短縮）
BEGIN
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
        uname      => 'HR',
        orig_table => 'EMPLOYEES',
        int_table  => 'EMPLOYEES_INTERIM'
    );
END;
/

-- テーブルを切り替えて完了する（短時間のロックが発生）
BEGIN
    DBMS_REDEFINITION.FINISH_REDEF_TABLE(
        uname      => 'HR',
        orig_table => 'EMPLOYEES',
        int_table  => 'EMPLOYEES_INTERIM'
    );
END;
/
-- FINISH 後: 元のテーブル名（EMPLOYEES）が新構造になる
--            暫定テーブル名（EMPLOYEES_INTERIM）が旧構造になる

-- 旧構造の暫定テーブルを削除する
DROP TABLE hr.employees_interim;

-- 何らかの問題で再定義を中断する場合（START 後、FINISH 前ならいつでも可能）
BEGIN
    DBMS_REDEFINITION.ABORT_REDEF_TABLE(
        uname      => 'HR',
        orig_table => 'EMPLOYEES',
        int_table  => 'EMPLOYEES_INTERIM'
    );
END;
/
-- ABORT 後に暫定テーブルと MVlog を手動で削除する
DROP TABLE hr.employees_interim;
-- マテリアライズドビューログも削除される（自動または手動）