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

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
----------
 12

SQL> select test_seq.nextval from dual;

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

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 ) again:

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
Advertisements
This entry was posted in ORA- ERRORS, SQL Scripts For DBA and tagged , , , , , , , , , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s