DBMS_REDEFINITION 是 Oracle 9i+ 提供的線上重定義套件，支援表結構、存儲屬性、分區策略、組織形式的全方位變更，全過程原表可正常 DML，僅最終切換瞬間需極短獨占鎖。本文提供標準化 8 步驟流程、關鍵參數詳解、異常處置與最佳實踐，是 7×24 系統 DBA 的必備技能。
MOVE 效果)GRANT EXECUTE_CATALOG_ROLE TO dba_user;
GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE TO dba_user;
-- 或由 SYS 直接執行| 方式 | 適用條件 | 副作用 | 推薦度 |
|---|---|---|---|
| 主鍵 (預設) | 表有主鍵或非空唯一鍵 | 無 | ⭐⭐⭐⭐⭐ |
| ROWID | 無主鍵表、或需保留 ROWID 相依應用 | 產生隱藏欄 M_ROW$$，IOT 不支援 | ⭐⭐ (備選) |
CAN_REDEF_TABLEEXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'SCHEMA',
orig_table => 'EMP',
options_flag => DBMS_REDEFINITION.CONS_USE_PK -- 或 CONS_USE_ROWID
);
-- 失敗拋出異常並給出原因 (如: 含 LONG/LOBS、MV 日誌、叢集表、SYS/SYSTEM 模式等)-- 範例：增加分區、調整存儲、新增欄位
CREATE TABLE emp_interim (
emp_id NUMBER PRIMARY KEY,
ename VARCHAR2(50),
dept_id NUMBER,
salary NUMBER(10,2),
hire_date DATE,
-- 新增欄位
email VARCHAR2(100),
status VARCHAR2(1) DEFAULT 'A'
)
PARTITION BY RANGE (hire_date) (
PARTITION p_2020 VALUES LESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD')),
PARTITION p_2021 VALUES LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD')),
PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD')),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
)
TABLESPACE users_data
PCTFREE 10 INITRANS 2 STORAGE (INITIAL 64K NEXT 1M);⚠️ 中間表不建立索引/約束/觸發器/權限，步驟 5 統一處理。
START_REDEF_TABLEEXEC DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'SCHEMA',
orig_table => 'EMP',
int_table => 'EMP_INTERIM',
col_mapping => 'EMP_ID EMP_ID, ENAME ENAME, DEPT_ID DEPT_ID, SALARY SALARY, HIRE_DATE HIRE_DATE,
NULL EMAIL, ''A'' STATUS', -- 映射規則：舊欄位→新欄位，新欄位用 NULL/常數
options_flag => DBMS_REDEFINITION.CONS_USE_PK
);
-- 關鍵點：
-- col_mapping 可省略 (預設所有中間表欄位自動映射同名舊欄位)
-- 若指定，則僅映射列出的欄位，未列出的舊欄位數據不遷移
-- 此步驟建立物化視圖日誌 (MLOG$_EMP) 記錄增量變更-- 索引 (本地分區索引範例)
CREATE INDEX emp_interim_dept_idx ON emp_interim(dept_id) LOCAL;
-- 約束 (外鍵、檢查約束) — 必須 DISABLE
ALTER TABLE emp_interim ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(dept_id) DISABLE;
ALTER TABLE emp_interim ADD CONSTRAINT chk_salary CHECK (salary > 0) DISABLE;
-- 觸發器
CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE INSERT OR UPDATE ON emp_interim
FOR EACH ROW BEGIN ... END;
-- 權限
GRANT SELECT, INSERT, UPDATE, DELETE ON emp_interim TO app_user;
-- 完成後：中間表約束 DISABLE，切換後自動 ENABLE；原表約束 ENABLE，切換後變為 DISABLE 並移至中間表SYNC_INTERIM_TABLE-- 若 START 與 FINISH 間隔較長、原表 DML 量大，建議執行 1-多次同步
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'SCHEMA',
orig_table => 'EMP',
int_table => 'EMP_INTERIM'
);
-- 作用：將 MLOG$_EMP 中記錄的增量變更應用到中間表，大幅減少 FINISH 時的鎖持有時間
-- 可在業務低峰定期排程執行FINISH_REDEF_TABLEEXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'SCHEMA',
orig_table => 'EMP',
int_table => 'EMP_INTERIM'
);
-- 關鍵瞬間：原表獨占鎖 (EXCLUSIVE LOCK)，持續時間 = 增量同步耗時 + 字典切換耗時
-- 通常 < 1 秒 (已同步情況) 至數秒 (未同步、大表)
-- 切換後：
-- 原表擁有中間表所有屬性、索引、約束 (ENABLE)、權限、觸發器
-- 中間表變為舊表結構、索引 DISABLE、約束 DISABLE
-- 原表相關 PL/SQL/游標 變為 INVALID，下次調用自動重編譯ALTER TABLE emp SET UNUSED (M_ROW$$);
ALTER TABLE emp DROP UNUSED COLUMNS;-- 任何階段出錯或人工中止
EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE(
uname => 'SCHEMA',
orig_table => 'EMP',
int_table => 'EMP_INTERIM'
);
-- 釋放物化視圖日誌、鎖、中間表可保留供調試或 DROPSELECT table_name, tablespace_name, partitioning_type FROM user_tables WHERE table_name = 'EMP'; → 確認分區、表空間SELECT index_name, partitioning_type FROM user_indexes WHERE table_name = 'EMP'; → 確認索引分區SELECT constraint_name, status FROM user_constraints WHERE table_name = 'EMP'; → 確認約束 ENABLESELECT COUNT(*) FROM emp PARTITION (p_2022); → 分區數據正確NOT NULL 約束LONG, BFILE, UDT (用戶定義類型) 欄位線上重定義是「換引擎不停飛機」的頂級技術。掌握 8 步驟標準流程、col_mapping 靈活映射、SYNC_INTERIM_TABLE 增量同步策略、FINISH 瞬間鎖控制、異常回滾機制，即可在 7×24 系統中從容完成表結構演進。請將此流程封裝為參數化腳本，納入變更管理體系，讓結構變更成為常態化、低風險的標準操作。
