ORA-08004: sequence SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated

ORA-08004: sequence MYSEQUENCE.NEXTVAL goes below MINVALUE and cannot be instantiated error occurs if the NEXTVAL of the sequence is less than the minimum value of the sequence. If a sequence is configured to decrement value, the sequence’s NEXTVAL will assign the current value a lower value. If the next value in the sequence is less than the minimum value, the sequence will not be able to assign a value that is less than the minimum value. Oracle will throw an error ORA-08004: sequence MYSEQUENCE.NEXTVAL goes below MINVALUE and cannot be instantiated in this case.

The sequence will begin with the value provided to the sequence start. The increment value will be used to generate the next value. If the increment value is negative, the next value subtracts from the current value and assigns the next value to the current value. If the next value is less than the minimum value, the sequence will be unable to discover a next value that is less than the sequence’s minimum value. The Oracle error ORA-08004: sequence MYSEQUENCE.NEXTVAL goes below MINVALUE and cannot be instantiated will be thrown.

The sequence minimum value should be changed if the sequence next value is less than the minimum value. The minimum value should be set lower than it is currently. The next value will be allocated to the maximum value if the sequence is set to cyclic sequence. These are the options for resolving the error.



The problem

If the sequence is configured to decrement value, the next value in the sequence will be less than the current value in the sequence. If the current value falls below the sequence’s minimum value, the sequence will be unable to discover a new value. The next value will be smaller than the sequence’s minimum value, which is not permitted. The sequence will give the error ORA-08004: sequence MYSEQUENCE.NEXTVAL goes below MINVALUE and cannot be instantiated if you try to find the NEXTVAL of the sequence.

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

-- call 2 times to decrement value that throws the error
select mysequence.nextval from dual; 
select mysequence.nextval from dual;

Error

ORA-08004: sequence MYSEQUENCE.NEXTVAL goes below MINVALUE 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

When the sequence’s current value reaches the minimum value, the sequence is unable to discover a value that is less than the minimum value. The minimum value should be changed to a lower number than it currently is. In this case, the sequence’s next value will be higher than the minimum value. The sequence’s range should be expanded by changing the sequence’s minimum value. To specify a lower minimum value, the sequence should be altered.

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

-- call 2 times to decrement value that throws the error
select mysequence.nextval from dual; 
select mysequence.nextval from dual; 

alter sequence MYSEQUENCE MINVALUE -5;

select mysequence.nextval from dual;

Output

0


Solution 2

If you don’t care about the minimum value, the integer datatype’s minimum value will be set in the sequence minimum value. The NOMINVALUE keyword is used to set the sequence minimum value to the minimal value of the integer datatype. If the sequence is configured to decrement value, it will do so until the integer datatype minimum value of -9999999999999999999999999999.

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

-- call 2 times to decrement value that throws the error
select mysequence.nextval from dual; 
select mysequence.nextval from dual; 

alter sequence MYSEQUENCE NOMINVALUE;

select mysequence.nextval from dual;

Output

0


Solution 3

If you don’t want to modify the minimum value, the cyclic sequence option is the next best thing. If the value hits the lowest value, the cyclic option will reset it to the maximum value. The sequence value will be decremented until the sequence minimum value is reached. After reaching the minimum value, the NEXTVAL will be given to the sequence maximum value. Because it rotates the sequence next value, the cycle option will never exhaust.

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

-- call 2 times to decrement value that throws the error
select mysequence.nextval from dual; 
select mysequence.nextval from dual; 

alter sequence MYSEQUENCE MINVALUE 1 cycle nocache;

select mysequence.nextval from dual;

Output

5



Leave a Reply