Oracle Sequence 完整指南：建立、修改、快取機制、跳號風險與最佳實踐

前言

Sequence 是 Oracle 生成唯一序號的標準機制，廣泛用於主鍵、單號、流水號。本文系統整理 CREATE/ALTER/DROP SEQUENCE 語法、CURRVAL/NEXTVAL 行為、CACHE/NOCAHCE 效能與跳號權衡、初始化參數影響，附實戰範例與避坑清單。

一、建立 Sequence：核心參數詳解

CREATE SEQUENCE emp_seq
  INCREMENT BY 1      -- 每次遞增值 (可負數遞減)
  START WITH 1        -- 起始值
  NOMAXVALUE          -- 無上限 (或 MAXVALUE 999999)
  MINVALUE 1          -- 下限 (遞減時生效)
  NOCYCLE             -- 到達極值報錯 (CYCLE 則循環)
  CACHE 20            -- 記憶體預取 20 個 (NOCAHCE 即時寫入磁碟)
  ORDER;              -- RAC 環境強制全局有序 (單實例忽略)

二、核心屬性：CURRVAL vs NEXTVAL

三、允許使用 Sequence 的 SQL 位置

• 無子查詢/視圖/快照的 SELECT 列表
• INSERT 的 VALUES 子句或子查詢
• UPDATE 的 SET 子句

禁止：WHERE、GROUP BY、ORDER BY、DISTINCT、UNION、CREATE TABLE AS SELECT、DEFAULT 子句 (12c+ 可用 DEFAULT seq.NEXTVAL)。

四、實戰範例

-- 插入員工，主鍵自動取號
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (emp_seq.NEXTVAL, 'LEWIS', 'CLERK', 7902, SYSDATE, 1200, NULL, 20);

-- 查詢剛插入的序號
SELECT emp_seq.CURRVAL FROM dual;

五、修改 Sequence：ALTER SEQUENCE

ALTER SEQUENCE emp_seq
  INCREMENT BY 10
  MAXVALUE 10000
  CYCLE
  NOCACHE;

• 可修改：INCREMENT BY、MAXVALUE/MINVALUE、CYCLE/NOCYCLE、CACHE/NOCACHE、ORDER/NOORDER
• 不可修改：START WITH —— 必須 DROP 重建
• 修改生效即時，但已快取的序號不受影響

六、CACHE 機制與跳號風險

原理

CACHE n 讓實例在 SGA 預先分配 n 個序號，用完再取下一批。單實例效能提升顯著；RAC 下各實例各自快取，導致全局序號「不連續但單調遞增」。

跳號場景

1. SHUTDOWN ABORT / 實例崩潰：記憶體中未用完的快取序號永久丟失
2. 手動 ALTER SEQUENCE ... NOCACHE 切換時
3. RAC 實例故障切換

選型建議

七、影響 Sequence 的初始化參數

• SEQUENCE_CACHE_ENTRIES：單實例可同時快取的 Sequence 數量上限 (預設 100/200 視版本)

八、刪除 Sequence

DROP SEQUENCE order_seq;

九、避坑清單

1. 同一 SQL 多次 NEXTVAL 值不同：INSERT ... VALUES (seq.NEXTVAL, seq.NEXTVAL) 會插入兩個不同號
2. CURRVAL 需先 NEXTVAL：新會話直接查 CURRVAL 報錯
3. CYCLE + 較小 MAXVALUE：循環後可能與現有主鍵衝突，需配合唯一索引防呆
4. 備份/恢復後序號倒退：冷備份還原會導致 Sequence 值回到備份時刻，新插入可能重複，**恢復後請手動 ALTER SEQUENCE INCREMENT BY 大數值 再改回**

十、總結

Sequence 是 Oracle 高併發唯一值生成的基石。**高併發用大 CACHE、業務連續用 NOCACHE、RAC 加 ORDER、備份後手動推進序號**。掌握這四條鐵律，即可游刃有餘應對各類編號需求。