ORA-08004: 序列 NEXTVAL exceeds MAXVALUE 无法实例化

sequence如果没有设置循环cycle，在超过了sequence的最大值后，将出现ORA-08004错误。 

SQL>  create sequence TEST_SEQ_NO_CYCLE
  2      minvalue 1
  3      maxvalue 200
  4      start with 100
  5      increment by 50
  6      nocache;

Sequence created

SQL> SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       100

SQL> SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       150

SQL> SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       200

SQL> SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL;

SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL

ORA-08004: 序列 TEST_SEQ_NO_CYCLE.NEXTVAL exceeds MAXVALUE 无法实例化

 

 

SQL> create sequence TEST_SEQ
  2  minvalue 1
  3  maxvalue 200
  4  start with 100
  5  increment by 50
  6  nocache
  7  cycle;

Sequence created

 

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       100

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       150

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       200

 

在达到了sequenc的最大值后，如果设置了循环cycle，sequence的初始值将从minvalue开始，而不是设置的start with 值。

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
         1

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
        51

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       101

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
       151

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
         1

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL
----------
        51