萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 修改非空欄位資料類型:臨時欄位遷移法完整步驟

場景

欄位 filedname NUMBER(2) NOT NULL 需改為 VARCHAR2(20),直接 ALTER TABLE MODIFY 會報錯 ORA-01439: column to be modified must be empty to change datatype。

標準遷移腳本(零停機風險版)

-- 1. 新增臨時欄位
ALTER TABLE tablename ADD filedname_temp NUMBER(2);

-- 2. 將原欄位值複製至臨時欄位,原欄位置 NULL
UPDATE tablename SET filedname_temp = filedname, filedname = NULL;
COMMIT;

-- 3. 修改原欄位型別(現為空,允許變更)
ALTER TABLE tablename MODIFY filedname VARCHAR2(20);

-- 4. 將資料遷回並清理臨時欄位
UPDATE tablename SET filedname = filedname_temp, filedname_temp = NULL;
COMMIT;

-- 5. 刪除臨時欄位
ALTER TABLE tablename DROP COLUMN filedname_temp;

-- 6. 恢復 NOT NULL 約束(如業務需要)
ALTER TABLE tablename MODIFY filedname NOT NULL;

-- 7. 驗證
SELECT * FROM tablename;

關鍵點解析

步驟關鍵動作原理
1ADD COLUMN新欄位無約束,直接加入
2UPDATE 雙向複製 + 置 NULL釋放原欄位「非空」限制,使其可改型別
3MODIFY DATATYPE空欄位可自由變更型別/長度
4回寫資料隱含型別轉換 (TO_CHAR),需確保資料相容
5DROP COLUMN清理中介物件
6ADD NOT NULL重新加上業務約束

效能與代價評估

  • 雙次全表掃描 UPDATE:產生大量 Undo/Redo,大表建議分批 WHERE ROWID BETWEEN ... 提交。
  • 鎖競爭:ALTER TABLE MODIFY 需排他 DDL 鎖,低峰期執行。
  • 索引/約束/視圖/觸發器:相依物件會失效,需重建/編譯。

替代方案:線上重定義 (DBMS_REDEFINITION)

-- 適合 7x24 不可停機大表
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCHEMA', 'TABNAME', 'INT_TAB', 
  'COL1 COL1, COL2 TO_CHAR(COL2) NEW_COL2 ...');
-- 同步、切換、完成

優點:零鎖定、業務無感;缺點:流程複雜、需雙倍空間、主鍵/物化視圖限制多。

最佳實踐建議

  1. 開發階段預留型別彈性(如直接用 VARCHAR2(4000) 或 NUMBER 無精度)。
  2. 變更前必備完整備份與回滾腳本。
  3. 測試環境全流程演練,驗證應用相容性(隱含轉換、PL/SQL 變量定義)。
  4. 納入變更管理流程,記錄變更單、審批、驗收簽署。

「臨時欄位遷移法」是修改非空欄位型別的通用標準解法,理解其「置空→改型→回填」核心邏輯,即可舉一反三應對各類型別變更場景。

copyright © 萬盛學電腦網 all rights reserved