您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 修改列數據類型三大實戰方案：從簡單到在線重定義的完整遷移指南

編輯：Oracle 資料庫基礎

場景定義

表 TB_TEST 列 PERMILE NUMBER(5) 需變更為 NUMBER(5,2)，且表中**已有業務數據**，不可簡單 ALTER TABLE MODIFY (報 ORA-01440)。

方案對比總覽

方案	適用場景	停機時間	空間開銷	風險點	複雜度
A. 臨時列 + 兩次更新	中小表 (<500萬行)、可接受短暫鎖表	分鐘級 (鎖表更新)	2x 列空間 + Undo/Redo	行遷移導致性能抖動、列順序變更影響 `SELECT *` 應用	低
B. 臨時列 + 置空 + 修改 + 回寫 (推薦通用)	中大表、需保持列順序、可接受雙倍 Undo/Redo	分鐘級 (兩次全表掃描)	2x 列空間 + 2x Undo/Redo	長事務風險、高併發下鎖爭用	中
C. 在線重定義 (DBMS_REDEFINITION)	7x24 無停機窗口、超大表 (>億行)、高併發核心表	近零 (僅切換瞬間排他鎖)	1.2x 表空間 (中間表)	物化視圖日志同步延遲、外鍵/觸發器/索引需手動遷移	高

方案 B 詳細腳本 (生產驗證版)


-- 1. 添加臨時列 (同名加後綴，類型為目標類型 NUMBER)
ALTER TABLE tb_test ADD permile_temp NUMBER;

-- 2. 解除原列非空約束 (若有)
ALTER TABLE tb_test MODIFY permile NULL;

-- 3. 第一階段遷移：數據拷貝至臨時列，原列置空
--    分批次提交避免 Undo 爆滿 (每 10 萬行提交)
DECLARE
  v_cnt NUMBER := 0;
BEGIN
  FOR r IN (SELECT rowid FROM tb_test WHERE permile IS NOT NULL) LOOP
    UPDATE tb_test SET permile_temp = permile, permile = NULL WHERE rowid = r.rowid;
    v_cnt := v_cnt + 1;
    IF MOD(v_cnt, 100000) = 0 THEN COMMIT; END IF;
  END LOOP;
  COMMIT;
END;
/

-- 4. 修改原列類型 (此時列為空，元數據操作秒級)
ALTER TABLE tb_test MODIFY permile NUMBER(5,2);

-- 5. 第二階段遷移：回寫數據
DECLARE
  v_cnt NUMBER := 0;
BEGIN
  FOR r IN (SELECT rowid FROM tb_test WHERE permile_temp IS NOT NULL) LOOP
    UPDATE tb_test SET permile = permile_temp, permile_temp = NULL WHERE rowid = r.rowid;
    v_cnt := v_cnt + 1;
    IF MOD(v_cnt, 100000) = 0 THEN COMMIT; END IF;
  END LOOP;
  COMMIT;
END;
/

-- 6. 清理臨時列
ALTER TABLE tb_test DROP COLUMN permile_temp;

-- 7. 恢復非空約束 (若業務要求)
ALTER TABLE tb_test MODIFY permile NOT NULL;

-- 8. 驗證
SELECT permile FROM tb_test WHERE ROWNUM <= 10;
SELECT column_name, data_type, data_precision, data_scale
FROM user_tab_columns WHERE table_name = 'TB_TEST' AND column_name = 'PERMILE';

方案 C：在線重定義核心步驟 (11g+)


-- 1. 驗證表可重定義 (需主鍵或唯一非空鍵)
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('MY_SCHEMA', 'TB_TEST', DBMS_REDEFINITION.CONS_USE_PK);

-- 2. 創建中間表 (目標結構)
CREATE TABLE tb_test_int (
  id        NUMBER PRIMARY KEY,
  permile   NUMBER(5,2),
  other_col VARCHAR2(100)
) TABLESPACE users;

-- 3. 啟動重定義
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
  uname        => 'MY_SCHEMA',
  orig_table   => 'TB_TEST',
  int_table    => 'TB_TEST_INT',
  col_mapping  => 'ID ID, PERMILE PERMILE, OTHER_COL OTHER_COL',
  options_flag => DBMS_REDEFINITION.CONS_USE_PK
);

-- 4. 創建輔助對象 (索引、約束、權限、觸發器) 於中間表
--    使用 COPY_TABLE_DEPENDENTS 自動複製 (12c+ 支持過濾)
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname        => 'MY_SCHEMA',
    orig_table   => 'TB_TEST',
    int_table    => 'TB_TEST_INT',
    num_errors   => num_errors,
    copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers=> TRUE,
    copy_constraints=> TRUE,
    copy_privileges=> TRUE
  );
  DBMS_OUTPUT.PUT_LINE('Errors: ' || num_errors);
END;
/

-- 5. 同步增量數據 (可重複執行，縮小最終切換窗口)
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MY_SCHEMA', 'TB_TEST', 'TB_TEST_INT');

-- 6. 完成切換 (極短排他鎖)
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MY_SCHEMA', 'TB_TEST', 'TB_TEST_INT');

-- 7. 清理
DROP TABLE tb_test_int;