Oracle DDL 与表设计

适用版本：Oracle Database 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

DDL（Data Definition Language）定义数据库结构[1]：

语句	说明
CREATE	创建
ALTER	修改
DROP	删除
TRUNCATE	截断
RENAME	重命名
COMMENT	注释

2. 创建表

2.1 基本语法

CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50) NOT NULL,
  last_name VARCHAR2(50) NOT NULL,
  email VARCHAR2(100) UNIQUE,
  phone VARCHAR2(20),
  hire_date DATE DEFAULT SYSDATE,
  job_id VARCHAR2(10) NOT NULL,
  salary NUMBER(8, 2),
  commission_pct NUMBER(2, 2),
  manager_id NUMBER,
  department_id NUMBER,
  CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) 
    REFERENCES departments(department_id),
  CONSTRAINT fk_emp_mgr FOREIGN KEY (manager_id) 
    REFERENCES employees(employee_id),
  CONSTRAINT chk_salary CHECK (salary > 0)
);

2.2 表空间

CREATE TABLE employees (
  ...
) TABLESPACE users;

2.3 存储参数

CREATE TABLE employees (
  ...
) STORAGE (
  INITIAL 1M
  NEXT 1M
  MINEXTENTS 1
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0
);

2.4 12c+ 默认值

CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(100),
  status VARCHAR2(20) DEFAULT ON NULL 'ACTIVE',
  create_date DATE DEFAULT ON NULL SYSDATE
);

3. 修改表

3.1 添加列

ALTER TABLE employees ADD (
  age NUMBER,
  address VARCHAR2(200)
);

3.2 修改列

ALTER TABLE employees MODIFY (
  email VARCHAR2(200) NOT NULL,
  salary NUMBER(10, 2)
);

3.3 删除列

ALTER TABLE employees DROP (age, address);

-- 标记 UNUSED（快）
ALTER TABLE employees SET UNUSED (age);

-- 后续删除
ALTER TABLE employees DROP UNUSED COLUMNS;

3.4 重命名列

ALTER TABLE employees RENAME COLUMN email TO email_address;

3.5 添加约束

ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);

3.6 修改约束

ALTER TABLE employees DISABLE CONSTRAINT chk_age;
ALTER TABLE employees ENABLE CONSTRAINT chk_age;

3.7 重命名表

RENAME employees TO emp;
-- 或
ALTER TABLE employees RENAME TO emp;

4. TRUNCATE

TRUNCATE TABLE employees;
-- 删除所有行，保留结构
-- 比 DELETE 快
-- 不能回滚

4.1 选项

-- 保留存储
TRUNCATE TABLE employees REUSE STORAGE;

-- 释放存储（默认）
TRUNCATE TABLE employees DROP STORAGE;

5. DROP

DROP TABLE employees;
-- 删除表和数据
-- 可回滚（回收站）

-- 级联约束
DROP TABLE employees CASCADE CONSTRAINTS;

-- 彻底删除
DROP TABLE employees PURGE;

6. 临时表

-- 会话级
CREATE GLOBAL TEMPORARY TABLE temp_emp (
  id NUMBER,
  name VARCHAR2(100)
) ON COMMIT PRESERVE ROWS;

-- 事务级
CREATE GLOBAL TEMPORARY TABLE temp_emp (
  ...
) ON COMMIT DELETE ROWS;

详细见：Oracle 临时表（Temporary Table）。

7. 表设计原则

7.1 范式

1NF：原子性
2NF：消除部分依赖
3NF：消除传递依赖
BCNF：增强 3NF

7.2 反范式

冗余字段
减少连接
提升读性能
牺牲写一致性

7.3 列设计

合理类型
NOT NULL 谨慎
DEFAULT 减少空值
主键必有
外键加索引

7.4 命名规范

表：复数（employees, departments）
列：单数（employee_id, last_name）
主键：pk_<table>
外键：fk_<table>_<ref>
唯一：uk_<table>_<col>
索引：idx_<table>_<col>

8. 表参数

8.1 PCTFREE / PCTUSED

CREATE TABLE employees (
  ...
) PCTFREE 20 PCTUSED 40;
-- PCTFREE 20: 保留 20% 用于 UPDATE
-- PCTUSED 40: 块使用降到 40% 才能插入

8.2 CACHE / NOCACHE

-- CACHE：常驻 Buffer Cache
CREATE TABLE lookup_table (...) CACHE;

-- NOCACHE（默认）
CREATE TABLE big_table (...) NOCACHE;

8.3 LOGGING / NOLOGGING

-- LOGGING（默认）：产生 redo
-- NOLOGGING：不产生 redo（批量加载快）
ALTER TABLE employees NOLOGGING;

8.4 PARALLEL

CREATE TABLE big_table (...) PARALLEL 4;

9. 在线重定义

-- DBMS_REDEFINITION
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'EMPLOYEES');

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
  uname => 'SCOTT',
  orig_table => 'EMPLOYEES',
  int_table => 'EMPLOYEES_NEW'
);

-- 同步
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMPLOYEES', 'EMPLOYEES_NEW');

-- 完成
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMPLOYEES', 'EMPLOYEES_NEW');

详细见：Oracle 在线重定义 DBMS_REDEFINITION。

10. 注释

COMMENT ON TABLE employees IS '员工信息表';
COMMENT ON COLUMN employees.salary IS '员工薪资，单位：元';

-- 查看
SELECT * FROM user_tab_comments WHERE table_name = 'EMPLOYEES';
SELECT * FROM user_col_comments WHERE table_name = 'EMPLOYEES';

11. 常见坑与排错

11.1 ORA-00955: 名称已被使用

-- 对象名冲突
-- 检查
SELECT * FROM user_objects WHERE object_name = 'EMPLOYEES';

11.2 ORA-02260: 表只能有一个主键

-- 检查已有主键
-- 删除旧主键

11.3 ORA-00942: 表不存在

-- 检查权限
-- 检查表名
-- 检查模式

11.4 ALTER 锁

-- DDL 阻塞 DML
-- 在线操作
ALTER TABLE employees ADD (col NUMBER) ONLINE;

12. 最佳实践

合理范式：平衡
主键必有：标识
外键加索引：避免锁
合理类型：节省空间
NOT NULL 谨慎：业务允许
DEFAULT 减少空值：易用
命名规范：统一
表空间分离：管理
大表分区：性能
在线 DDL：避免阻塞

13. 参考资料

[1] Oracle Database SQL Language Reference 19c, “CREATE TABLE” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html