Oracle修改seuqnce當前值的三種方法

更新時間：2026年03月05日 09:05:11 作者：瀟湘隱者

本文介紹了三種調(diào)整Oracle數(shù)據(jù)庫序列當前值的方法,增加、減少序列當前值；直接使用SQL調(diào)整序列當前值；以及刪除并重建序列,還討論了序列越界的問題及其解決方法,以及不能修改序列初始值的注意事項,感興趣的朋友跟隨小編一起看看吧

方法1

其實這種方法調(diào)整序列的當前值,其實就是增加或減少序列(SEQUENCE)的當前值, 語法如下

ALTER SEQUENCE SEQUENCE_NAME INCREMENT BY XXX; ----正數(shù)負數(shù)都可以

具體案例如下所示:

SQL> CREATE SEQUENCE SEQ_TEST
  2  INCREMENT BY 1
  3  START WITH 1
  4  MINVALUE 1 NOMAXVALUE
  5  NOCYCLE;
Sequence created.
SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
   NEXTVAL
----------
         1
SQL> /
   NEXTVAL
----------
         2
SQL> /
   NEXTVAL
----------
         3
SQL> /
   NEXTVAL
----------
         4
SQL> 
SQL> SELECT SEQ_TEST.CURRVAL FROM DUAL;
   CURRVAL
----------
         4
SQL>

此時由于一些原因,想將序列SEQ_TEST的當前值調(diào)整為100, 那么要如何做呢?

SQL> ALTER SEQUENCE SEQ_TEST INCREMENT BY 96;
Sequence altered.
SQL> SELECT SEQ_TEST.CURRVAL FROM DUAL;
   CURRVAL
----------
         4
SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
   NEXTVAL
----------
       100
SQL> 
SQL> ALTER SEQUENCE SEQ_TEST INCREMENT BY -80;
Sequence altered.
SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
   NEXTVAL
----------
        20
SQL>

方法2

如果數(shù)據(jù)庫版本為12.1 或以上版本,可以使用下面SQL調(diào)整序列的當前值.

ALTER SEQUENCE <SEQUENCE_NAME> RESTART START WITH xxx;

例子:

SQL> ALTER SEQUENCE SEQ_TEST RESTART START WITH 200;
Sequence altered.
SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
   NEXTVAL
----------
       200
SQL> 
SQL> ALTER SEQUENCE SEQ_TEST RESTART START WITH 120;
Sequence altered.
SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
   NEXTVAL
----------
       120
SQL>

這種方法相對于第一種方法更簡潔與方便. 不需要你去計算增加或減少序列的大小值.

方法3

這種方法簡單粗暴, 直接DROP掉序列,然后重建序列. 這里就不過多贅述了.

答疑解惑

問題1:

ORA-08004: sequence SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated

SQL> DROP SEQUENCE SEQ_TEST;
Sequence dropped.
SQL> CREATE SEQUENCE SEQ_TEST
  2   INCREMENT BY 1
  3   START WITH 1
  4   MINVALUE 1 NOMAXVALUE
  5   NOCYCLE;
Sequence created.
SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
   NEXTVAL
----------
         1
SQL> /
   NEXTVAL
----------
         2
SQL> /
   NEXTVAL
----------
         3
SQL> ALTER SEQUENCE SEQ_TEST INCREMENT BY -20;
Sequence altered.
SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
SELECT SEQ_TEST.NEXTVAL FROM DUAL
       *
ERROR at line 1:
ORA-08004: sequence SEQ_TEST.NEXTVAL goes below MINVALUE and cannot be instantiated
SQL>

出現(xiàn)這種問題,即序列的越界, 這種一般發(fā)生在向后遞增,而且LAST_NUMBER小于MIN_VALUE的情況下.如下所示:

SQL> SET LINESIZE 255
SQL> COL SEQUENCE_OWNER FOR A16;
SQL> COL SEQUENCE_NAME FOR A30;
SQL> COL MAX_VALUE FOR 9999999999999999999999999999999999;
SQL> SELECT SEQUENCE_OWNER, SEQUENCE_NAME,MIN_VALUE,MAX_VALUE, LAST_NUMBER  
  2  FROM DBA_SEQUENCES
  3  WHERE SEQUENCE_NAME=UPPER(TRIM('&SEQUENCE_NAME'));
Enter value for sequence_name: SEQ_TEST
old   3: WHERE SEQUENCE_NAME=UPPER(TRIM('&SEQUENCE_NAME'))
new   3: WHERE SEQUENCE_NAME=UPPER(TRIM('SEQ_TEST'))
SEQUENCE_OWNER   SEQUENCE_NAME                   MIN_VALUE                           MAX_VALUE LAST_NUMBER
---------------- ------------------------------ ---------- ----------------------------------- -----------
SYS              SEQ_TEST                                1        9999999999999999999999999999         -17
SQL>

其實如果你用第二種方法是不會遇到,它直接會出錯并提示,而使用第一種方法則會遇到這種情況,你需要計算序列的當前值往后回退的過程中,它的值應該大于MIN_VALUE
還有一種報錯是ORA-08004,超過MAXVALUE 無法實例化.這個是另外一種情況.

SQL> ALTER SEQUENCE SEQ_TEST RESTART START WITH -100;
ALTER SEQUENCE SEQ_TEST RESTART START WITH -100
*
ERROR at line 1:
ORA-04006: START WITH cannot be less than MINVALUE

問題2:

SQL> ALTER SEQUENCE SEQ START WITH 1000;
ALTER SEQUENCE SEQ START WITH 1000
                   *
ERROR at line 1:
ORA-02283: cannot alter starting sequence number

注意,不能修改序列的初始值,否則會報ORA-02283.如需所示:

$ oerr ora 02283
02283, 00000, "cannot alter starting sequence number"
// *Cause: Self-evident.
// *Action: Don't alter it.

The error ORA-02283: cannot alter starting sequence number occurs in Oracle when you attempt to directly modify the START WITH value of
an existing sequence. Oracle does not allow this operation for an already created sequence. However, there are workarounds to achieve the
desired result.

如果想修改序列的初始值,可以drop掉當前序列,然后重建序列.

到此這篇關于Oracle修改seuqnce的當前值(三種方法)的文章就介紹到這了,更多相關oracle修改seuqnce值內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: