ORACLE如何修改SEUQNCE的当前值

在一些特殊场景(业务需求)可能需要修改序列(SEQUENCE)的当前值(CURRVAL)的大小, 有可能调大,也有可能调小, 这里简单介绍一下.
方法1

其实这种方法调整序列的当前值,其实就是增加或减少序列(SEQUENCE)的当前值, 语法如下

1. ALTER SEQUENCE SEQUENCE_NAME INCREMENT BY XXX; ----正数负数都可以

复制代码

具体案例如下所示:

1. SQL> CREATE SEQUENCE SEQ_TEST
2.   2  INCREMENT BY 1
3.   3  START WITH 1
4.   4  MINVALUE 1 NOMAXVALUE
5.   5  NOCYCLE;
7. Sequence created.
9. SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
11.    NEXTVAL
12. ----------
13.          1
15. SQL> /
17.    NEXTVAL
18. ----------
19.          2
21. SQL> /
23.    NEXTVAL
24. ----------
25.          3
27. SQL> /
29.    NEXTVAL
30. ----------
31.          4
33. SQL>
34. SQL> SELECT SEQ_TEST.CURRVAL FROM DUAL;
36.    CURRVAL
37. ----------
38.          4
40. SQL>

复制代码

此时由于一些原因,想将序列SEQ_TEST的当前值调整为100, 那么要如何做呢?

1. SQL> ALTER SEQUENCE SEQ_TEST INCREMENT BY 96;
3. Sequence altered.
5. SQL> SELECT SEQ_TEST.CURRVAL FROM DUAL;
7.    CURRVAL
8. ----------
9.          4
11. SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
13.    NEXTVAL
14. ----------
15.        100
17. SQL>
18. SQL> ALTER SEQUENCE SEQ_TEST INCREMENT BY -80;
20. Sequence altered.
22. SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
24.    NEXTVAL
25. ----------
26.         20
28. SQL>

复制代码

方法2

如果数据库版本为12.1 或以上版本,可以使用下面SQL调整序列的当前值.

1. ALTER SEQUENCE <SEQUENCE_NAME> RESTART START WITH xxx;

复制代码

例子:

1. SQL> ALTER SEQUENCE SEQ_TEST RESTART START WITH 200;
3. Sequence altered.
5. SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
7.    NEXTVAL
8. ----------
9.        200
11. SQL>
12. SQL> ALTER SEQUENCE SEQ_TEST RESTART START WITH 120;
14. Sequence altered.
16. SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
18.    NEXTVAL
19. ----------
20.        120
22. SQL>

复制代码

这种方法相对于第一种方法更简洁与方便. 不需要你去计算增加或减少序列的大小值.


方法3

这种方法简单粗暴, 直接DROP掉序列,然后重建序列. 这里就不过多赘述了.

答疑解惑

问题1:

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

1. SQL> DROP SEQUENCE SEQ_TEST;
3. Sequence dropped.
5. SQL> CREATE SEQUENCE SEQ_TEST
6.   2   INCREMENT BY 1
7.   3   START WITH 1
8.   4   MINVALUE 1 NOMAXVALUE
9.   5   NOCYCLE;
11. Sequence created.
13. SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
15.    NEXTVAL
16. ----------
17.          1
19. SQL> /
21.    NEXTVAL
22. ----------
23.          2
25. SQL> /
27.    NEXTVAL
28. ----------
29.          3
31. SQL> ALTER SEQUENCE SEQ_TEST INCREMENT BY -20;
33. Sequence altered.
35. SQL> SELECT SEQ_TEST.NEXTVAL FROM DUAL;
36. SELECT SEQ_TEST.NEXTVAL FROM DUAL
37.        *
38. ERROR at line 1:
39. ORA-08004: sequence SEQ_TEST.NEXTVAL goes below MINVALUE and cannot be instantiated
42. SQL>

复制代码

出现这种问题,即序列的越界, 这种一般发生在向后递增,而且LAST_NUMBER小于MIN_VALUE的情况下.如下所示:

1. SQL> SET LINESIZE 255
2. SQL> COL SEQUENCE_OWNER FOR A16;
3. SQL> COL SEQUENCE_NAME FOR A30;
4. SQL> COL MAX_VALUE FOR 9999999999999999999999999999999999;
5. SQL> SELECT SEQUENCE_OWNER, SEQUENCE_NAME,MIN_VALUE,MAX_VALUE, LAST_NUMBER  
6.   2  FROM DBA_SEQUENCES
7.   3  WHERE SEQUENCE_NAME=UPPER(TRIM('&SEQUENCE_NAME'));
8. Enter value for sequence_name: SEQ_TEST
9. old   3: WHERE SEQUENCE_NAME=UPPER(TRIM('&SEQUENCE_NAME'))
10. new   3: WHERE SEQUENCE_NAME=UPPER(TRIM('SEQ_TEST'))
12. SEQUENCE_OWNER   SEQUENCE_NAME                   MIN_VALUE                           MAX_VALUE LAST_NUMBER
13. ---------------- ------------------------------ ---------- ----------------------------------- -----------
14. SYS              SEQ_TEST                                1        9999999999999999999999999999         -17
16. SQL>

复制代码

其实如果你用第二种方法是不会遇到,它直接会出错并提示,而使用第一种方法则会遇到这种情况,你需要计算序列的当前值往后回退的过程中,它的值应该大于MIN_VALUE
还有一种报错是ORA-08004,超过MAXVALUE 无法实例化.这个是另外一种情况.

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

复制代码

问题2:

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

复制代码

注意,不能修改序列的初始值,否则会报ORA-02283.如需所示:

1. $ oerr ora 02283
2. 02283, 00000, "cannot alter starting sequence number"
3. // *Cause: Self-evident.
4. // *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掉当前sequence,然后重建.
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！