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

The error ORA-08004: sequence MYSEQUENCE.NEXTVAL exceeds MAXVALUE and cannot be instantiated occurs when the sequence NEXTVAL returns a value that is greater than the sequence maximum value. The sequence’s next value is a next generated value using the sequence configuration. The sequence’s next value will start from the sequence’s minimum value and reaches the maximum value. The sequence will be reset with the sequence’s minimum value if the sequence is cyclic and the next value exceeds the sequence’s maximum value. The error will be thrown if the sequence is not cyclic and the next value exceeds the sequence’s maximum value.

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

The sequence will not be allowed to increment to the next value if the NEXTVAL of the sequence exceeds the sequence maximum value. An error will be thrown by Oracle. Between the minimum and maximum values, the sequence will increment. The sequence starts with the smallest value and progresses to the largest value. If the sequence’s current value approaches the maximum value, it will not be allowed to increment to the next value that is more than the 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

If the NEXTVAL of the sequence exceeds the sequence maximum value, the sequence cannot increment the next value that is more than the maximum value. The sequence value range has reached its limit. If the maximum value is altered to extend the range of sequence values, the sequence’s NEXTVAL can find the next value. The sequence maximum value should be increased to cover the sequence range.

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 concerned about the maximum value, you can alter the sequence to set the highest possible value. NOMAXVALUE can be used to specify the sequence’s maximum value. The datatype’s maximum value is used as the sequence’s maximum value. NOMAXVALUE is set to 9999999999999999999999999999. If you set the sequence’s maximum value to NOMAXVALUE, the range will seldom exhaust.

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

If the sequence should be used with a fixed range of values, the cycle option can be used to reuse the sequence values. If the sequence’s NEXTVAL is greater than the sequence’s maximum value, the NEXT VAL is set to the sequence’s minimum value. Each cycle in the NEXTVAL of the sequence uses the same range of values.

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