Oracle 9i 線上表格重定義實戰：DBMS_REDEFINITION 零停機重組表結構

前言：從離線到線上的跨越

Oracle 9i 前，表結構變更（加欄、改型別、調整儲存參數）僅能透過 匯出/匯入 (EXP/IMP) 或 建新表+重命名，皆需獨佔鎖 (Exclusive Lock)，導致應用停機。9i 引入 DBMS_REDEFINITION 套件，實現線上表格重定義，讓 7x24 系統也能零停機重組表結構。

一、核心原理：影子表 + 變更捕獲 + 同步切換

原始表 (TITLES)          中間表 (TITLES2)           同步機制
┌──────────────┐         ┌──────────────┐
│  資料 + 結構  │ ─────►  │  新結構 + 複製資料  │  CTAS 建立
└──────────────┘         └──────────────┘
        │                        │
        ▼                        ▼
   啟用 Materialized View Log   記錄 DML 變更
   (內部機制)                    (佇列)
        │                        │
        └──────────┬─────────────┘
                   ▼
         SYNC_INTERIM_TABLE 定期同步
                   │
                   ▼
         FINISH_REDEF_TABLE 原子切換
                   │
                   ▼
              完成重定義

二、完整操作流程實戰

場景：為 TITLES 表新增主鍵、調整 royalty 欄位計算邏輯

步驟 0：環境檢查與清理

-- 若有殘留重定義，先中止
EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE('PUBS', 'TITLES', 'TITLES2');

-- 確認原表有主鍵（重定義前提）
ALTER TABLE titles ADD CONSTRAINT pk_titles PRIMARY KEY (title_id);

-- 檢查是否可重定義
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('PUBS', 'TITLES');

步驟 1：建立中間表 (CTAS)

CREATE TABLE titles2 AS
SELECT * FROM titles;

步驟 2：啟動重定義 (START_REDEF_TABLE)

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
  uname       => 'PUBS',
  orig_table  => 'TITLES',
  int_table   => 'TITLES2',
  col_mapping => 'title_id title_id, 
                  title title, 
                  type type, 
                  pub_id pub_id, 
                  price price, 
                  advance advance, 
                  royalty*1.1 royalty,  -- 業務邏輯調整：royalty 增加 10%
                  ytd_sales ytd_sales, 
                  notes notes, 
                  pubdate pubdate'
);

關鍵參數： col_mapping 定義欄位映射與轉換邏輯，支援運算式。

步驟 3：建立輔助物件 (索引、約束、觸發器等)

-- 在中間表建立相同索引/約束（名稱需不同）
CREATE INDEX idx_titles2_type ON titles2(type);
ALTER TABLE titles2 ADD CONSTRAINT pk_titles2 PRIMARY KEY (title_id);

步驟 4：定期同步變更 (SYNC_INTERIM_TABLE)

-- 可多次執行，減少最終切換時的同步量
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('PUBS', 'TITLES', 'TITLES2');

步驟 5：完成重定義 (FINISH_REDEF_TABLE)

-- 短暫鎖定原表（通常 < 1 秒），原子切換名稱
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('PUBS', 'TITLES', 'TITLES2');

步驟 6：清理

DROP TABLE titles2;

三、失敗恢復與中止機制

-- 任何階段失敗，必須先中止釋放快照
EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE('PUBS', 'TITLES', 'TITLES2');

-- 參數：schema, original_table, interim_table
-- 作用：釋放 Materialized View Log、釋放鎖、清理中間狀態

四、關鍵限制條件 (9i/10g/11g 通用)

五、進階應用：分區表重定義

-- 將非分區表轉為範圍分區表
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
  uname => 'SALES',
  orig_table => 'ORDERS',
  int_table => 'ORDERS_PART',
  col_mapping => '*',  -- 所有欄位保持不變
  options_flag => DBMS_REDEFINITION.CONS_USE_PK
);

-- 中間表預先建立分區結構
CREATE TABLE orders_part (
  order_id NUMBER, order_date DATE, ...
)
PARTITION BY RANGE (order_date) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
  PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01','YYYY-MM-DD'))
);

六、監控與驗證

-- 查看重定義狀態
SELECT * FROM dba_redefinitions;

-- 查看中間表同步進度
SELECT * FROM dba_redef_errors;

-- 驗證資料一致性
SELECT COUNT(*) FROM pubs.titles;
SELECT COUNT(*) FROM pubs.titles2;  -- 完成前

七、12c+ 簡化語法：REDEF_TABLE 一鍵完成

BEGIN
  DBMS_REDEFINITION.REDEF_TABLE(
    uname           => 'PUBS',
    tname           => 'TITLES',
    table_part_tablespace => 'USERS',
    table_compression_type => 'ROW STORE COMPRESS ADVANCED',
    lob_compression_type => 'COMPRESS HIGH',
    column_mapping  => 'royalty*1.1 royalty'
  );
END;
/

結語

DBMS_REDEFINITION 是 Oracle 線上維護的利器。掌握 啟動 → 同步 → 切換 三階段流程、理解 Materialized View Log 捕獲變更 原理、熟悉 限制條件與失敗恢復，讓 DBA 能在業務零感知下完成表結構演進。12c+ 的 REDEF_TABLE 更將複雜度大幅降低，建議升級後優先採用新介面。