Oracle 表結構修改進階：列操作、無用列、註釋與最佳實踐

Oracle 表結構修改進階：列操作、無用列、註釋與最佳實踐

表結構變更是數據庫生命週期中的常態操作。本文深入講解 ALTER TABLE 的列增加、修改、刪除、標記無用、註釋管理等進階用法，並給出生產環境變更的關鍵注意事項。

準備環境

DROP TABLE member PURGE;
CREATE TABLE member (
  mid  NUMBER(5),
  name VARCHAR2(50) DEFAULT '無名氏'
);
INSERT INTO member(mid, name) VALUES (1, '張三');
INSERT INTO member(mid, name) VALUES (2, '李四');
INSERT INTO member(mid, name) VALUES (3, '王五');

一、增加列（ADD）

ALTER TABLE member ADD (age NUMBER(3));
ALTER TABLE member ADD (sex VARCHAR2(10) DEFAULT '男');
ALTER TABLE member ADD (photo VARCHAR2(100) DEFAULT 'nophoto.jpg');
DESC member;  -- 驗證三列已加入

新增列默認值僅對後續 INSERT 生效，現有行保持 NULL（除非指定 DEFAULT 並配合 11g+ 的元數據優化）。

二、修改列（MODIFY）

-- 修改長度：縮短需確保現有數據不超限
ALTER TABLE member MODIFY (name VARCHAR2(30));

-- 修改默認值與長度
ALTER TABLE member MODIFY (sex VARCHAR2(3) DEFAULT '女');
DESC member;

風險提示： 修改列數據類型（如 VARCHAR2→NUMBER）要求列為空或數據完全兼容，否則報錯。生產環境建議：新建列→遷移數據→刪舊列→重命名。

三、刪除列（DROP COLUMN）

ALTER TABLE member DROP COLUMN photo;
ALTER TABLE member DROP COLUMN sex;
DESC member;

10g+ 支持 DROP COLUMN 物理移除，但會掃描全表清理數據，大表極慢且產生大量 REDO。優先考慮「標記無用 + 維護窗口物理刪除」。

四、標記無用列（SET UNUSED）與物理清理

-- 標記無用：極快，僅更新數據字典
ALTER TABLE member SET UNUSED COLUMN age;
DESC member;  -- age 不再顯示
SELECT * FROM member;  -- 查詢結果不含 age

-- 物理清理無用列：在低峰期執行
ALTER TABLE member DROP UNUSED COLUMNS;

優勢對比

五、註釋管理（COMMENT）

-- 表註釋
COMMENT ON TABLE member IS '這是學習用的會員表';
SELECT * FROM user_tab_comments WHERE table_name = 'MEMBER';

-- 列註釋
COMMENT ON COLUMN member.mid      IS '會員編號';
COMMENT ON COLUMN member.name     IS '會員姓名';
COMMENT ON COLUMN member.age      IS '會員年齡';
COMMENT ON COLUMN member.birthday IS '會員生日';
SELECT * FROM user_col_comments WHERE table_name = 'MEMBER';

良好的註釋是數據治理的基石，建議納入發布流程強制要求。

生產環境變更清單

1. 變更前：導出表結構、統計信息、依賴對象（視圖/觸發器/包/同義詞）。
2. 評估影響：DBA_TAB_COLS、DBA_DEPENDENCIES、應用代碼掃描。
3. 大表採用「在線重定義」（DBMS_REDEFINITION）或「版本化修改」避免長鎖。
4. 變更後：驗證數據完整性、重新收集統計、刷新依賴對象、更新數據字典文檔。