Oracle 資料庫物件包含表、視圖、索引、序列、同義詞、約束等。本文系統整理 DDL 建立、修改、刪除、重命名語法與最佳實務。
命名規則
- 以字母開頭,可含字母、數字、
_、$、#,長度 ≤ 128 位元組 (12cR2 起 128 字元)。
- 同一 Schema 物件名唯一,避免保留字 (需雙引號強制使用)。
建立表 (CREATE TABLE)
CREATE TABLE [schema.]table (
col1 datatype [DEFAULT expr] [constraint],
col2 datatype,
...
[table_constraint]
);
範例
CREATE TABLE scott.test1 (
name VARCHAR2(20),
hiredate DATE DEFAULT SYSDATE,
salary NUMBER(8,2) DEFAULT 0
);
子查詢建表 (CTAS)
CREATE TABLE myemp2 AS
SELECT empno, ename, sal*12 AS annsal FROM emp;
修改表結構 (ALTER TABLE)
| 操作 | 語法 | 限制 |
| 新增欄位 | ADD (col datatype [DEFAULT expr]) | 只能加到最後 |
| 修改欄位 | MODIFY (col datatype [DEFAULT expr]) | 不可改名;有資料時型態/長度變更受限 |
| 刪除欄位 | DROP (col1, col2...) | 釋放空間;12c+ 可 DROP UNUSED COLUMNS |
| 設定未使用 | SET UNUSED (col) | 標記未用,稍後批次釋放空間 |
清空與刪除表
TRUNCATE TABLE t;:DDL、不可回滾、釋放 High Water Mark、重設序號 (Identity)。
DROP TABLE t [CASCADE CONSTRAINTS] [PURGE];:移除表、索引、約束、觸發器;PURGE 略過回收筒。
重命名
RENAME old_name TO new_name; -- 表、視圖、序列、同義詞皆適用
約束條件 (Constraints)
| 類型 | 關鍵字 | 層級 | 說明 |
| 非空 | NOT NULL | 欄位級 | 唯一只能欄位級 |
| 唯一 | UNIQUE | 欄位/表級 | 允許多個 NULL |
| 主鍵 | PRIMARY KEY | 欄位/表級 | 隱含 NOT NULL+UNIQUE,每表僅一個 |
| 外鍵 | FOREIGN KEY ... REFERENCES | 表級 | 參照父表 PK/UK,可指定 ON DELETE CASCADE/SET NULL |
| 檢查 | CHECK (condition) | 欄位/表級 | 條件不可包含子查詢、SYSDATE、NEXTVAL 等 |
建表時加約束
CREATE TABLE stu (
sid NUMBER PRIMARY KEY,
name VARCHAR2(20) NOT NULL,
dept_id NUMBER,
CONSTRAINT fk_stu_dept FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);
事後新增/刪除/啟用/停用
ALTER TABLE stu ADD CONSTRAINT uk_stu_name UNIQUE (name);
ALTER TABLE stu DROP CONSTRAINT fk_stu_dept CASCADE; -- 連帶刪除子表 FK
ALTER TABLE stu DISABLE CONSTRAINT uk_stu_name;
ALTER TABLE stu ENABLE CONSTRAINT uk_stu_name;
視圖 (VIEW)
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias,...)] AS subquery
[WITH CHECK OPTION [CONSTRAINT name]]
[WITH READ ONLY [CONSTRAINT name]];
FORCE:基表不存在也能建立。
WITH CHECK OPTION:DML 需符合視圖 WHERE 條件。
WITH READ ONLY:唯讀視圖。
索引 (INDEX)
CREATE [UNIQUE|BITMAP] INDEX idx_name ON table(col1, col2 DESC)
[TABLESPACE ts] [COMPRESS n] [ONLINE];
- B*Tree (預設) 適合高基數;Bitmap 適合低基數 (DW 環境)。
ONLINE:11g+ 線上建立不鎖表。
序列 (SEQUENCE)
CREATE SEQUENCE seq_name
INCREMENT BY 1 START WITH 1
MAXVALUE 999999 MINVALUE 1
CACHE 20 NOCYCLE;
CACHE:預先產生號碼放記憶體,效能佳但重啟會跳號。
NEXTVAL/CURRVAL 取值。
同義詞 (SYNONYM)
CREATE [PUBLIC] SYNONYM syn_name FOR [schema.]object_name;
- Public 同義詞需
CREATE PUBLIC SYNONYM 權限,所有使用者可見。
- 簡化跨 Schema 存取:
SELECT * FROM emp_syn; 代替 SELECT * FROM scott.emp;。
資料字典查詢
-- 當前使用者物件
SELECT object_name, object_type FROM user_objects;
-- 所有可存取物件
SELECT * FROM all_tables;
-- DBA 檢視所有 (需 DBA 權限)
SELECT * FROM dba_tables;
-- 約束明細
SELECT * FROM user_constraints WHERE table_name='STU';
SELECT * FROM user_cons_columns WHERE table_name='STU';