Oracle 12c+ Identity Column 指定值插入實作：IDENTITY_INSERT 替代方案與最佳實務

重要釐清：Oracle 無 IDENTITY_INSERT 指令

原文提及的 SET IDENTITY_INSERT ON 是 SQL Server 語法。Oracle 12c 引入 IDENTITY 欄位後，機制與 SQL Server 不同，不支援 臨時開啟指定值插入的開關。

Oracle Identity Column 運作機制

CREATE TABLE test_identity_gaps (
  id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  description VARCHAR2(20)
);

• GENERATED ALWAYS：永遠由資料庫自動產生，不可手動指定值（預設）
• GENERATED BY DEFAULT：允許插入時指定值，若未指定則自動產生
• GENERATED BY DEFAULT ON NULL：僅在插入 NULL 時自動產生

解決「補齊流水號缺口」的三種方案

方案一：使用 GENERATED BY DEFAULT（推薦新表設計）

CREATE TABLE test_flexible (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  description VARCHAR2(20)
);

-- 正常插入（自動產生）
INSERT INTO test_flexible (description) VALUES ('Auto 1');

-- 指定值插入（補缺口）
INSERT INTO test_flexible (id, description) VALUES (5, 'Manual 5');

-- 後續自動產生會從序列當前值繼續（不會倒退）
INSERT INTO test_flexible (description) VALUES ('Auto 2'); -- id=6

注意：若手動插入值大於序列當前值，後續自動產生會跳過該值，不會重複。

方案二：重建序列對齊（既有 GENERATED ALWAYS 表）

Oracle Identity 底層使用 Sequence。若必須在 GENERATED ALWAYS 表插入指定值：

-- 1. 查詢身分欄位對應的序列名
SELECT table_name, column_name, sequence_name
FROM user_tab_identity_cols
WHERE table_name = 'TEST_IDENTITY_GAPS';

-- 假設序列為 ISEQ$$_123456

-- 2. 臨時修改序列起始值（需 DBA 權限）
ALTER SEQUENCE ISEQ$$_123456 INCREMENT BY 1000;

-- 3. 插入指定值（利用觸發器或應用層繞過限制，較複雜）
-- 實務上建議改用方案三

缺點：操作風險高、影響並發、序列修改需獨佔鎖。

方案三：業務邏輯層面接受「不連續」（最佳實務）

流水號缺口是正常現象，原因包括：

• 交易回滾（Rollback）
• 序列快取失效（Cache 遺失）
• 刪除資料
• 高可用性切換

建議做法：

1. 業務單據號與資料庫 PK 分離（PK 純技術鍵，單據號由業務邏輯產生）
2. 審計需求用 FLASHBACK、LOG_MINER、觸發器記錄而非依賴 PK 連續性
3. 若法規強制連續，改用「號碼簿表」自行管理號碼分配

序列與 Identity 進階管理

-- 查看序列當前值
SELECT ISEQ$$_123456.CURRVAL FROM DUAL;

-- 取得下一值（不插入資料）
SELECT ISEQ$$_123456.NEXTVAL FROM DUAL;

-- 修改序列屬性
ALTER SEQUENCE ISEQ$$_123456 NOCACHE;  -- 防止重啟跳號
ALTER SEQUENCE ISEQ$$_123456 CACHE 20; -- 提高效能

-- 12c+ 直接管理 Identity 序列
ALTER TABLE test_identity_gaps MODIFY id GENERATED BY DEFAULT AS IDENTITY;
-- 或
ALTER TABLE test_identity_gaps MODIFY id GENERATED ALWAYS AS IDENTITY;

遷移建議：從舊版 Trigger+Sequence 升級

-- 舊版寫法
CREATE SEQUENCE seq_dept;
CREATE TRIGGER trg_dept_bir
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
  SELECT seq_dept.NEXTVAL INTO :new.dept_id FROM DUAL;
END;

-- 12c+ 現代寫法
CREATE TABLE dept (
  dept_id NUMBER GENERATED ALWAYS AS IDENTITY,
  dept_name VARCHAR2(50)
);

優勢：效能更好（無觸發器開銷）、語意清晰、支援 IMPORT/EXPORT 正確處理序列值。

總結對照表