標準語法
添加列
ALTER TABLE table_name ADD (column_name datatype [DEFAULT expr] [CONSTRAINT constraint_def]);
刪除列
-- 立即物理刪除 (10g+)
ALTER TABLE table_name DROP COLUMN column_name;
-- 兩階段刪除 (大表推薦，避免長時間鎖表)
ALTER TABLE table_name SET UNUSED COLUMN column_name;
-- 維護窗口執行
ALTER TABLE table_name DROP UNUSED COLUMNS;
實戰範例
-- 添加帶默認值的非空列 (大表瞬間完成，僅更新字典)
ALTER TABLE t_point_manage ADD (add_points_reason NUMBER(8) DEFAULT 0 NOT NULL);
-- 添加多列
ALTER TABLE t_point_manage ADD (
create_by VARCHAR2(30) DEFAULT USER,
create_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_by VARCHAR2(30),
update_dt TIMESTAMP
);
-- 刪除單列
ALTER TABLE text_attr_detail DROP COLUMN add_points_reason;
-- 刪除多列 (12c+)
ALTER TABLE text_attr_detail DROP (col1, col2, col3);
跨 Schema 操作權限要求
-- 當前用戶非表擁有者時，需指定 Schema 並擁有權限
ALTER TABLE hr.employees ADD (middle_name VARCHAR2(20));
-- 所需權限：
-- 1. ALTER ANY TABLE 系統權限 (DBA 角色擁有)
-- 2. 或表擁有者授予: GRANT ALTER ON hr.employees TO app_user;
大表變更最佳實踐
Online DDL 技術 (12c+)
-- 12c+ 許多 DDL 操作支持 ONLINE 模式，不阻塞 DML
ALTER TABLE huge_table ADD (new_col VARCHAR2(100)) ONLINE;
ALTER TABLE huge_table DROP COLUMN old_col ONLINE; -- 需先 SET UNUSED
零停機變更模式
- 添加列：指定 DEFAULT 值 → 瞬間完成 (僅字典更新)
- 修改列類型：創建新列 → 同步數據 → 重命名切換 → 刪除舊列
- 刪除列：SET UNUSED → 應用層停用 → 維護窗口 DROP UNUSED COLUMNS
- 重命名列：12c+
RENAME COLUMN 支持 ONLINE
空間回收策略
-- 刪除列後空間不自動回收，需重組
ALTER TABLE table_name MOVE; -- 鎖表操作
-- 或 Online 重組 (需 Enterprise Edition)
DBMS_REDEFINITION.START_REDEF_TABLE(...);
約束與索引聯動處理
| 場景 | 自動行為 | 手動處理 |
|---|
| 刪除主鍵列 | 報錯 ORA-02266 | 先刪除主鍵約束: ALTER TABLE ... DROP PRIMARY KEY CASCADE |
| 刪除外鍵引用列 | 報錯 ORA-02298 | 先刪除外鍵約束 |
| 刪除唯一索引列 | 索引自動刪除 | 無需手動 |
| 刪除普通索引列 | 索引自動標記 UNUSABLE | 需 REBUILD 或 DROP |
變更驗證清單
- ☑
DESC table_name 確認列定義
- ☑
SELECT * FROM user_tab_cols WHERE table_name = 'TABLE_NAME'; 檢查數據類型、默認值、NULLABLE
- ☑
SELECT * FROM user_constraints WHERE table_name = 'TABLE_NAME'; 確認約束狀態
- ☑
SELECT * FROM user_indexes WHERE table_name = 'TABLE_NAME'; 確認索引狀態
- ☑ 應用層測試：插入/查詢/更新新列，驗證邏輯正確
- ☑ 統計信息收集：
DBMS_STATS.GATHER_TABLE_STATS 更新統計信息
常見錯誤代碼速查
| 錯誤 | 原因 | 解決 |
|---|
| ORA-01430 | 列已存在 | 檢查列名拼寫、大小寫 |
| ORA-00904 | 標識符無效 | 列名包含保留字/特殊字符，需雙引號 |
| ORA-02266 | 主鍵列不可刪除 | 先 DROP PRIMARY KEY CASCADE |
| ORA-02298 | 外鍵約束阻止 | 先 DROP 外鍵約束 |
| ORA-01735 | 無效 ALTER TABLE 選項 | 語法錯誤，檢查括號、關鍵字拼寫 |
