ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

Recycle the sequence to start from minimum value by “alter sequence cycle”.

SQL> CREATE SEQUENCE test_seq
     START WITH 1
     INCREMENT BY 1
     MAXVALUE 10; 

Sequence created.
SQL> select test_seq.currval from dual;
 select test_seq.currval from dual
 *
ERROR at line 1:
ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session

SQL> select test_seq.nextval from dual;

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

SQL> /

NEXTVAL
----------
 2

...
..
.

NEXTVAL
----------
 9

SQL> /

NEXTVAL
----------
 10

SQL> /
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

1)Increase maximum value:

SQL> alter sequence test_seq maxvalue 15;

Sequence altered.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 11

..
.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 14

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 15

SQL> select test_seq.nextval from dual;
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

2)Increment by -1.

SQL> alter sequence test_seq increment by -1;

Sequence altered.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 14

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 13

...
..
.

SQL> select test_seq.nextval from dual;

NEXTVAL
----------
 2

SQL> select test_seq.nextval from dual;

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

SQL> select test_seq.nextval from dual;
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated

3) Recycle the sequence starting from minimum value ( 1 ) by:

alter sequence cycle
SQL>select test_seq.nextval from dual;


NEXTVAL
----------
 15

SQL> /
 select test_seq.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated


SQL> select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,
            INCREMENT_BY,CACHE_SIZE,CYCLE_FLAG 
      from  dba_sequences 
     where  SEQUENCE_NAME='TEST_SEQ';

SEQUENCE_NAM MIN_VALUE  MAX_VALUE  INCREMENT_BY CACHE_SIZE C
------------ ---------- ---------- ------------ ---------- -
TEST_SEQ     1          15         1            20         N

SQL> alter sequence TEST_SEQ cycle cache 10;

Sequence altered.

SQL> select test_seq.nextval from dual ;

NEXTVAL
----------
 1
Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: