Oracle Sequence 重置為指定起始值的多種方法 (不刪除重建)

核心原理

利用 ALTER SEQUENCE ... INCREMENT BY 動態調整步長：

1. 查詢當前 NEXTVAL (假設為 curr)。
2. 將 INCREMENT BY 設為 target_start - curr (若目標較小則為負值)。
3. 呼叫一次 NEXTVAL，序列值跳躍至目標起始值。
4. 將 INCREMENT BY 恢復為正常步長 (通常為 1)。

優點：避免 DROP/CREATE 導致相依物件 (函數、觸發器、存儲過程) 失效需重新編譯。

方法一：匿名 PL/SQL 區塊 (指定目標起始值與步長)

DECLARE
    n           NUMBER(10);
    v_startnum  NUMBER(10) := 10000001;  -- 目標起始值
    v_step      NUMBER(10) := 1;         -- 正常步長
    tsql        VARCHAR2(200);
    v_seqname   VARCHAR2(200) := 'MIP_JF_SEQUENCE';
BEGIN
    -- 1. 取得當前 NEXTVAL
    EXECUTE IMMEDIATE 'SELECT ' || v_seqname || '.NEXTVAL FROM dual' INTO n;
    
    -- 2. 計算需調整的步長 (目標 - 當前 - 正常步長)
    n := v_startnum - n - v_step;
    
    -- 3. 臨時調整步長
    tsql := 'ALTER SEQUENCE ' || v_seqname || ' INCREMENT BY ' || n;
    EXECUTE IMMEDIATE tsql;
    
    -- 4. 執行一次 NEXTVAL 跳躍到目標值
    EXECUTE IMMEDIATE 'SELECT ' || v_seqname || '.NEXTVAL FROM dual' INTO n;
    
    -- 5. 恢復正常步長
    tsql := 'ALTER SEQUENCE ' || v_seqname || ' INCREMENT BY ' || v_step;
    EXECUTE IMMEDIATE tsql;
END;
/

方法二：通用重置存儲過程 (歸零/歸一)

CREATE OR REPLACE PROCEDURE seq_reset (v_seqname VARCHAR2) AS
    n    NUMBER(10);
    tsql VARCHAR2(100);
BEGIN
    -- 取得當前值
    EXECUTE IMMEDIATE 'SELECT ' || v_seqname || '.NEXTVAL FROM dual' INTO n;
    
    -- 計算負步長：-(當前值 - 1) → 下一次 NEXTVAL 變為 1
    n := -(n - 1);
    
    tsql := 'ALTER SEQUENCE ' || v_seqname || ' INCREMENT BY ' || n;
    EXECUTE IMMEDIATE tsql;
    
    -- 執行跳躍
    EXECUTE IMMEDIATE 'SELECT ' || v_seqname || '.NEXTVAL FROM dual' INTO n;
    
    -- 恢復步長 1
    tsql := 'ALTER SEQUENCE ' || v_seqname || ' INCREMENT BY 1';
    EXECUTE IMMEDIATE tsql;
END seq_reset;
/

驗證範例

SQL> CREATE SEQUENCE seq_1 INCREMENT BY 1 START WITH 1 MAXVALUE 999999999;
序列已創建。

SQL> SELECT seq_1.NEXTVAL FROM dual;  -- 1
SQL> /  -- 2
SQL> /  -- 3
SQL> /  -- 4
SQL> /  -- 5

SQL> EXEC seq_reset('seq_1');
PL/SQL 過程已成功完成。

SQL> SELECT seq_1.CURRVAL FROM dual;
CURRVAL
---------
1        -- 成功重置為 1

方法三：手動逐步執行 (最簡單、無需建物件)

-- 假設序列名 seq_name，當前 NEXTVAL 為 5656，欲重置為 1

1. SELECT seq_name.NEXTVAL FROM dual;     -- 記下結果，如 5656
2. ALTER SEQUENCE seq_name INCREMENT BY -5655;  -- -(5656-1)
3. SELECT seq_name.NEXTVAL FROM dual;     -- 變為 1
4. ALTER SEQUENCE seq_name INCREMENT BY 1;  -- 恢復步長

注意事項

• 併發環境：執行期間若有其他 Session 同時取值，可能導致最終值偏移。建議在維護窗口或鎖定序列期間執行。
• START WITH 不可修改：此法僅影響後續產生值，不改變定義中起始值。
• CACHE 影響：若序列啟用快取，重置後快取區剩餘號碼仍會被消耗，可能出現不連續，但不影響唯一性。
• 適用於 單實例；RAC 環境下各實例各有快取，需謹慎評估。

對比：DROP/CREATE vs INCREMENT BY 法