基礎表結構準備
CREATE TABLE t_class_info (
classno NUMBER(3) PRIMARY KEY,
classname VARCHAR2(10),
classplace VARCHAR2(13)
);
CREATE TABLE t_student_info (
stuno NUMBER(3),
classno NUMBER(3),
stuname VARCHAR2(10),
stusex CHAR(1),
stucontect NUMBER(11),
stuadd VARCHAR2(50),
studetailinfo VARCHAR2(100)
);
一、列級操作
| 操作 | 語法示例 | 說明 |
|---|
| 重命名列 | ALTER TABLE t_class_info RENAME COLUMN classplace TO location; | 10g+ 支持，無需重建表 |
| 新增列 | ALTER TABLE t_student_info ADD (stuid VARCHAR2(18), stuage DATE DEFAULT SYSDATE NOT NULL); | 可帶 DEFAULT/NOT NULL，11g+ 元數據級瞬時完成 |
| 刪除列 | ALTER TABLE t_student_info DROP COLUMN studetailinfo; | 10g+ 物理刪除，12c+ 支持 DROP UNUSED COLUMNS 延遲清理 |
| 修改長度/精度 | ALTER TABLE t_class_info MODIFY classplace VARCHAR2(50);
ALTER TABLE t_student_info MODIFY stuno NUMBER(2); | 擴大通常安全，縮小需確保現有數據符合 |
| 修改數據類型 | ALTER TABLE t_student_info MODIFY stusex CHAR(2); | 隱式轉換需兼容，建議先加新列遷移數據再切換 |
| 修改默認值 | ALTER TABLE t_student_info MODIFY stuage DEFAULT SYSDATE+1; | 僅影響後續插入 |
二、約束管理
| 類型 | 添加 | 啟用/禁用 | 刪除 | 延遲約束 |
|---|
| 主鍵 | ADD CONSTRAINT pk_stu_no PRIMARY KEY(stuno) | ENABLE/DISABLE CONSTRAINT pk_stu_no | DROP CONSTRAINT pk_stu_no | ADD CONSTRAINT fk_classno FOREIGN KEY(classno) REFERENCES t_class_info(classno) DEFERRABLE INITIALLY DEFERRED;
提交時檢查，適合批量導入 |
| 外鍵 | ADD CONSTRAINT fk_classno FOREIGN KEY(classno) REFERENCES t_class_info(classno) | ENABLE/DISABLE CONSTRAINT fk_classno | DROP CONSTRAINT fk_classno |
| 檢查 | ADD CONSTRAINT ck_stu_info CHECK (stusex IN ('F','M')) | ENABLE/DISABLE CONSTRAINT ck_stu_info | DROP CONSTRAINT ck_stu_info |
| 非空 | MODIFY stucontect CONSTRAINT nn_stu_contect NOT NULL | 同上 | DROP CONSTRAINT nn_stu_contect |
| 唯一 | ADD CONSTRAINT uq_stu_contect UNIQUE(stucontect) | 同上 | DROP CONSTRAINT uq_stu_contect |
三、註釋與清理
-- 表註釋
COMMENT ON TABLE t_student_info IS '學生表';
-- 列註釋
COMMENT ON COLUMN t_student_info.stuname IS '學生姓名';
COMMENT ON COLUMN t_class_info.classname IS '班級';
-- 清空表數據（保留結構、釋放高水位、不可回滾）
TRUNCATE TABLE t_student_info;
-- 刪除表（連結約束、索引、權限一併刪除）
DROP TABLE t_student_info [CASCADE CONSTRAINTS];
四、架構師級最佳實踐
- 大表加列帶 DEFAULT NOT NULL：11g+ 瞬時元數據操作，無鎖表風險；10g 需評估鎖表時間。
- 類型變更零停機模式：
ADD new_col → UPDATE ... SET new_col = CAST(old_col AS new_type) → RENAME COLUMNS → DROP old_col，配合版本發布窗口。
- 約束狀態變更：大數據量啟用外鍵/檢查約束時，先
ENABLE NOVALIDATE 避免全表掃描，再離線 VALIDATE。
- DDL 審計：啟用
AUDIT DDL 或 DDL 觸發器記錄結構變更，納入變更管理流程。
- PCTFREE/PCTUSED/STORAGE：
ALTER TABLE ... MOVE TABLESPACE ... 重組表時同步調優存儲參數。
五、版本特性對照表
| 特性 | 10g | 11g | 12c | 19c/21c |
|---|
| RENAME COLUMN | ✅ | ✅ | ✅ | ✅ |
| ADD COLUMN + DEFAULT NOT NULL (元數據級) | ❌ 鎖表 | ✅ 瞬時 | ✅ 瞬時 | ✅ 瞬時 |
| DROP COLUMN 物理刪除 | ✅ | ✅ | ✅ | ✅ |
| SET UNUSED / DROP UNUSED COLUMNS | ✅ | ✅ | ✅ | ✅ |
| INVISIBLE COLUMNS | ❌ | ❌ | ✅ | ✅ |
| READ ONLY TABLE | ❌ | ❌ | ✅ | ✅ |
