Oracle Sequence 重置歸零兩種實作方案

Sequence 無 RESET 指令，需透過調整 INCREMENT BY 正負值技巧歸零。兩種封裝方案：

方案 1：匿名區塊 (臨時執行)

DECLARE
  n       NUMBER;
  v_sql   VARCHAR2(200);
  p_seq   VARCHAR2(30) := 'SEQ_RUN_ID';
BEGIN
  -- 1. 取得下一值
  EXECUTE IMMEDIATE 'SELECT ' || p_seq || '.NEXTVAL FROM dual' INTO n;
  -- 2. 計算負增量 (使下一值變 1)
  n := -(n - 1);
  v_sql := 'ALTER SEQUENCE ' || p_seq || ' INCREMENT BY ' || n;
  EXECUTE IMMEDIATE v_sql;
  -- 3. 取一次讓序列推進到 1
  EXECUTE IMMEDIATE 'SELECT ' || p_seq || '.NEXTVAL FROM dual' INTO n;
  -- 4. 恢復增量為 1
  v_sql := 'ALTER SEQUENCE ' || p_seq || ' INCREMENT BY 1';
  EXECUTE IMMEDIATE v_sql;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
```

方案 2：永久預存程序 (可重用)
CREATE OR REPLACE PROCEDURE reset_sequence(p_seq_name IN VARCHAR2) IS
  n     NUMBER;
  v_sql VARCHAR2(200);
BEGIN
  EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.NEXTVAL FROM dual' INTO n;
  n := -(n - 1);
  v_sql := 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY ' || n;
  EXECUTE IMMEDIATE v_sql;
  EXECUTE IMMEDIATE 'SELECT ' || p_seq_name || '.NEXTVAL FROM dual' INTO n;
  v_sql := 'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY 1';
  EXECUTE IMMEDIATE v_sql;
EXCEPTION
  WHEN OTHERS THEN
    -- 建議記錄日誌而非靜默
    DBMS_OUTPUT.PUT_LINE('Reset ' || p_seq_name || ' failed: ' || SQLERRM);
    RAISE;
END reset_sequence;
/
```

呼叫範例
EXEC reset_sequence('SEQ_ORDER_ID');

關鍵原理圖解
現狀：NEXTVAL = 1001
步驟1：INCREMENT BY -1000  →  下一值 = 1
步驟2：SELECT NEXTVAL      →  回傳 1，序列現值 = 1
步驟3：INCREMENT BY 1      →  恢復正常遞增

注意事項

需具備 ALTER ANY SEQUENCE 或序列擁有者權限。
高併發環境下重置會導致序列值衝突，**務必在維護窗口單執行緒執行**。
12c+ 支援 RESTART 子句：ALTER SEQUENCE seq RESTART START WITH 1; (僅限無快取/無循環序列)，更簡潔。
若序列設定 CACHE，重置後快取值可能殘留，建議先 ALTER SEQUENCE ... NOCACHE 再重置，完成後恢復 CACHE。


掌握負增量技巧或 12c RESTART，Sequence 歸零不再手忙腳亂。