ORA-04007: MINVALUE cannot be made to exceed the current value

ORA-04007: MINVALUE cannot be made to exceed the current value error occurs if the sequence minimum value is modified to a value that exceeds the current sequence value. You attempt to change the sequence’s minimum value to anything greater than the sequence’s current value.

The sequence current value will be somewhere between the sequence’s minimum and maximum values. When the NEXTVAL command is executed, the sequence current value is incremented as per increment configuration. If the minimum value is larger than the current value, the NEXTVAL function will fail to determine the next value. The error message ORA-04007: MINVALUE cannot be made to exceed the current value will be shown.

Cause

the given MINVALUE would be greater than the current value

Action

always make sure that MINVALUE is <= the current value

The Problem

You attempt to raise the sequence minimum value above the sequence current value. The CURRVAL command returns the current value of the sequence. Because the sequence’s current value is outside of the sequence’s range between the minimum and maximum values, the sequence’s NEXTVAL could not find the sequence’s next value.

create sequence mysequence start with 1 minvalue 1;
alter sequence mysequence minvalue 5;

Error

Error starting at line : 3 in command -
alter sequence mysequence minvalue 5
Error report -
ORA-04007: MINVALUE cannot be made to exceed the current value
04007. 00000 -  "MINVALUE cannot be made to exceed the current value"
*Cause:    the given MINVALUE would be greater than the current value
*Action:   always make sure that MINVALUE is <= the current value

Solution 1

The sequence current value must be identified in order to fix this error. The sequence’s minimal value should be changed less than the sequence’s current value. Reduce the sequence minimum value to a value less than the current value to resolve this error.

select mysequence.CURRVAL from duel;
-- returns 4
alter sequence mysequence minvalue 3;

Solution 2

If the sequence’s minimum value to be changed to a value greater than the current value, first increment the current value, then modify the sequence to the minimum value. The current value must be greater than the minimum value in the sequence. The current value is increased until it reaches the minimum value, at which point it is changed to the required value.

Solution

select mysequence.NEXTVAL from dual;  -- execute 5 times

alter sequence mysequence minvalue 5;

Solution 3

We cannot manually call NEXTVAL if the difference between the sequence current value and the required minimum value is large. The value of the sequence increment should be increased. The sequence NEXTVAL should be used to get the necessary minimum value. The increment value should be returned to its original value. The sequence’s minimum value can be changed to whatever value is required.

alter sequence mysequence increment by 5;

select mysequence.NEXTVAL from dual;

alter sequence mysequence increment by 1;

alter sequence mysequence minvalue 5;