在一些特殊場景(業務需求)可能需要修改序列(SEQUENCE)的當前值(CURRVAL)的大小, 有可能調大,也有可能調小, 這裏簡單介紹一下.
方法1
其實這種方法調整序列的當前值,其實就是增加或減少序列(SEQUENCE)的當前值, 語法如下
ALTER SEQUENCE SEQUENCE_NAME INCREMENT BY XXX; ----正數負數都可以
具體案例如下所示:
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的當前值調整為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
如果數據庫版本為12.1 或以上版本,可以使用下面SQL調整序列的當前值.
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>
出現這種問題,即序列的越界, 這種一般發生在向後遞增,而且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掉當前序列,然後重建序列.
