Oracle 核心物件深度解析：同義詞、序列與視圖的建立、管理與實戰應用

一、同義詞：提供物件的別名與位置透明性

1. 基本建立語法

CREATE [PUBLIC] SYNONYM synonym_name FOR [schema.]object_name[@dblink];

2. 三大核心價值

• 隱藏物件真實擁有者與名稱：SELECT * FROM hr.employees → CREATE SYNONYM emp FOR hr.employees; SELECT * FROM emp;
• 分散式資料庫位置透明性：CREATE SYNONYM remote_emp FOR hr.employees@prod_link; 讓應用無感知存取遠端物件。
• 公共存取入口：CREATE PUBLIC SYNONYM pub_emp FOR hr.employees; 所有使用者皆可直接存取。

3. 私有 vs 公有優先順序

同名稱下 私有同義詞優先於公有同義詞（類似區域變數遮蔽全域變數），可利用此特性在測試環境覆蓋生產公有同義詞。

4. 管理操作

-- 查詢
SELECT * FROM user_synonyms;        -- 私有
SELECT * FROM all_synonyms;         -- 可存取
SELECT * FROM dba_synonyms;         -- 全部 (需 DBA 權限)

-- 刪除
DROP SYNONYM emp;                   -- 私有
DROP PUBLIC SYNONYM pub_emp;        -- 公有

二、序列：產生唯一序號的標準化引擎

1. 完整建立語法與參數詳解

CREATE SEQUENCE schema.seq_name
  INCREMENT BY 1          -- 間距 (正數遞增、負數遞減)，預設 1
  START WITH 1            -- 起始值，預設 = MINVALUE
  MAXVALUE 9999999999     -- 最大值，預設 NOMAXVALUE (10^27)
  MINVALUE 1              -- 最小值，預設 NOMINVALUE (-10^26)
  CYCLE | NOCYCLE         -- 到達極值是否循環，預設 NOCYCLE
  CACHE 20 | NOCACHE;     -- 記憶體預取數量，預設 CACHE 20 (效能最佳)

2. 兩大偽欄位

• NEXTVAL：取得下一個值，每次呼叫遞增/遞減。
• CURRVAL：取得當前 Session 已取得的值（需先呼叫過 NEXTVAL）。

INSERT INTO t(id, data) VALUES (seq.NEXTVAL, 'xxx');
SELECT seq.CURRVAL FROM dual;

3. 修改與刪除

ALTER SEQUENCE seq_name INCREMENT BY 5 MAXVALUE 10000;  -- 不可修改 START WITH
DROP SEQUENCE seq_name;

4. 實戰陷阱與最佳實踐

• CACHE 導致間隙：實例重啟、備份還原、跨節點 (RAC) 會造成序號跳號，不可作為無間隙業務單號（如發票號），需自建編號表。
• 高併發調大 CACHE：如 CACHE 1000 減少序列鎖競爭，但風險跳號更大。
• 跨 Session CURRVAL 不共享：每個 Session 維護自己的 CURRVAL。

三、視圖：封裝查詢邏輯、實現資料安全與簡化開發

1. 建立語法

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name [(col_alias,...)] AS
  subquery
  [WITH CHECK OPTION [CONSTRAINT constraint_name]]
  [WITH READ ONLY [CONSTRAINT constraint_name]];

• FORCE：基表不存在也能建立視圖（稍後補建基表）。
• WITH READ ONLY：禁止透過視圖 DML，確保唯讀。
• WITH CHECK OPTION：DML 必須符合視圖定義的 WHERE 條件。

2. 經典實戰：視圖實作資料行級安全

場景：HR.EMPLOYEES 含薪資敏感欄位，全員需查詢通訊錄但不可看薪資。

-- 1. 建立唯讀通訊錄視圖
CREATE VIEW company_phone_book AS
  SELECT first_name || ' ' || last_name AS name,
         email, phone_number
  FROM   hr.employees
  WITH READ ONLY;

-- 2. 授權 PUBLIC 僅能存取視圖
GRANT SELECT ON company_phone_book TO PUBLIC;

-- 3. 驗證
DESC company_phone_book;  -- 僅顯示 NAME, EMAIL, PHONE_NUMBER
SELECT * FROM company_phone_book;  -- 成功
SELECT salary FROM company_phone_book;  -- ORA-00904: invalid identifier

此模式實現最小權限原則：應用/使用者只見視圖定義欄位，基表敏感欄位完全隔離。

3. 視圖管理查詢

-- 視圖清單
SELECT view_name FROM user_views;

-- 視圖定義原始碼 (長文本需設定 LONG)
SET LONG 20000
SELECT text FROM user_views WHERE view_name = 'COMPANY_PHONE_BOOK';

總結

善用這三大物件，能讓資料庫架構更清晰、安全性更完善、應用開發更高效。建議將同義詞、序列、視圖的命名規範納入團隊開發標準，並定期審視未使用物件進行清理。