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

ORA-08004: sequence MYSEQUENCE.NEXTVAL exceeds MAXVALUE and cannot be instantiated error occurs when the sequence NEXTVAL provides a value that is bigger than the sequence maximum value. The sequence maximum value is the sequence’s biggest value. If the NEXTVAL returns a value that is more than the maximum sequence value, an error ORA-08004: sequence MYSEQUENCE.NEXTVAL exceeds MAXVALUE and cannot be instantiated will be thrown.

The minimum value will be used to start the Oracle sequence. The sequence’s NEXTVAL will increase the next value depending on the increment value set in the sequence. The sequence value will be increased until the sequence maximum value is reached.

When the current value of a sequence exceeds the sequence maximum value, the sequence’s NEXTVAL cannot be incremented to the next value. Oracle will throw the error ORA-08004: sequence MYSEQUENCE.NEXTVAL exceeds MAXVALUE and cannot be instantiated in this case.

The user sequences table contains the sequence minimum, maximum, and increment values. The sequence’s CURRVAL and NEXTVAL can be compared to the maximum value to find the error. Increase the maximum value or use the cyclic increment of the sequence value to fix the error.



The problem

If the NEXTVAL of the sequence exceeds the sequence maximum value, the sequence will not be able to increment to the next value. Oracle will thrown an error. The sequence will increment values in between minimum and maximum numbers. The sequence begins with minimum value and reaches the maximum value. If the current value of the sequences reaches the maximum value, sequence will not be able to increment to the next value that is more than maximum value.

create sequence mysequence start with 1 increment by 1 MAXVALUE 5 nocycle;

--call 5 times to increment the next value
select mysequence.nextval from dual; 
select mysequence.nextval from dual; 
select mysequence.nextval from dual; 
select mysequence.nextval from dual; 
select mysequence.nextval from dual; 

-- fails in 6th attempt
select mysequence.nextval from dual; 

Error

ORA-08004: sequence MYSEQUENCE.NEXTVAL exceeds MAXVALUE and cannot be instantiated
08004. 00000 -  "sequence %s.NEXTVAL %s %sVALUE and cannot be instantiated"
*Cause:    instantiating NEXTVAL would violate one of MAX/MINVALUE
*Action:   alter the sequence so that a new value can be requested


Solution 1

The sequence cannot increment the next value that is bigger than the maximum value if the NEXTVAL of the sequence exceeds the sequence maximum value. The sequence value range is exhausted. The NEXTVAL of the sequence can find the next value if the maximum value is changed to increase the range of sequence values. To span the sequence range, the sequence maximum value should be increased.

Find the maximum value

select * from user_sequences where sequence_name = 'MYSEQUENCE';

Code

create sequence mysequence start with 1 increment by 1 MAXVALUE 5 nocycle;

ALTER SEQUENCE mysequence MAXVALUE 10 ;

select mysequence.nextval from dual; -- try 6 times, in the 6th attempt will show 6.

Output

6


Solution 2

If you don’t care about the maximum value, you may change the sequence to set the highest possible value. NOMAXVALUE can be used to set the sequence maximum value. The datatype’s maximum value is used as the sequence maximum value. The default NOMAXVALUE is 9999999999999999999999999999. The range will rarely exhaust if you set maximum value of the sequence is NOMAXVALUE.

Code

create sequence mysequence start with 1 increment by 1 MAXVALUE 5 nocycle;

ALTER SEQUENCE mysequence NOMAXVALUE ;

select mysequence.nextval from dual; -- try 6 times, in the 6th attempt will show 6.

Output

6


Solution 3

The sequence values can be reused by using the cycle option if the sequence should be used with a limited range of values. If the sequence’s NEXTVAL exceeds the sequence’s maximum value, it returns the sequence’s minimum value as the NEXT VAL. In the NEXTVAL of the sequence, the same range of values is used in each cycle.

create sequence mysequence start with 1 increment by 1 MAXVALUE 5 cycle;

OR

create sequence mysequence start with 1 increment by 1 MAXVALUE 5 nocycle;
alter sequence MYSEQUENCE cycle cache 4; -- Cache should be less than max value. by default 20

Code

select mysequence.nextval from dual; -- try 6 times, in the 6th attempt will show 1.

Output

1