Oracle 線上重定義表實戰：使用 DBMS_REDEFINITION 修改欄位與資料轉換

應用場景

7×24 業務系統中，需對線上大表進行結構變更（如欄位重命名、資料轉換）且不中斷 DML 操作。Oracle 9i 提供的 DBMS_REDEFINITION 套件可滿足此需求。

完整操作步驟

1. 建立原始測試表

CREATE TABLE red_test AS
SELECT te_seq.nextval AS id, t.owner, t.object_name, 10 AS tvalue
FROM dba_objects t;

ALTER TABLE red_test ADD CONSTRAINT pri_redtest PRIMARY KEY (id);
CREATE INDEX idc_retest_owner ON red_test(owner);

需求：將 tvalue 重命名為 sal，並使 sal = tvalue * 10。

2. 驗證是否可線上重定義

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('ETL', 'RED_TEST', DBMS_REDEFINITION.CONS_USE_PK);

3. 建立中間表（目標結構）

CREATE TABLE RED_TEST_MIDDLE AS
SELECT id, owner, object_name, tvalue AS sal FROM red_test WHERE 1=2;

ALTER TABLE RED_TEST_MIDDLE ADD CONSTRAINT pri_redtest123 PRIMARY KEY (id);

4. 啟動線上重定義

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
  'ETL', 'RED_TEST', 'RED_TEST_MIDDLE',
  'ID ID, OWNER OWNER, OBJECT_NAME OBJECT_NAME, TVALUE*10 SAL'
);

5. 同步中間表（捕獲重定義期間的 DML 變更）

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ETL', 'RED_TEST', 'RED_TEST_MIDDLE');

6. 測試原始表持續 DML 是否同步

INSERT INTO RED_TEST SELECT te_seq.nextval, t.owner, t.object_name, 999 FROM dba_objects t;
COMMIT;

SELECT COUNT(*) FROM MLOG$_RED_TEST; -- 查看 Materialized View Log 記錄筆數

7. 完成重定義

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('ETL', 'RED_TEST', 'RED_TEST_MIDDLE');

8. 驗證結果與索引狀態

檢查索引發現：重定義僅同步資料，索引不會自動遷移。正式環境建議於第 5 步驟前先於中間表建立所需索引。

9. 清理中間表

DROP TABLE RED_TEST_MIDDLE;

風險提示與替代方案

對於 5GB 以上的大表線上重定義至分區表，耗時可能達數小時，且存在失敗導致業務癱瘓的風險。較保險的做法（需停機）：

1. Export 原表資料
2. 建立新分區表
3. Import 資料並指定 IGNORE=Y

除非必要，建議謹慎評估後再使用線上重定義。