ORA-02283: cannot alter starting sequence number

ORA-02283: cannot alter starting sequence number error occurs when you use the START WITH option to change the current value. You’re attempting to reset the sequence’s current value by using the sequence START WITH option.

In some occasions, the sequence current value is required to reset or starts with different series of values. The oracle sequence will not allow to reset the sequence current value using START WITH option. If you try to change current value using START WITH option, the sequence will throw an error ORA-02283: cannot alter starting sequence number.

Cause

Self-evident.

Action

Don’t alter it.



The Problem

The sequence current value may be required to reset. In some cases, the sequence should start with a new series of values. The sequence’s current value must be changed to the desired value. The Oracle sequence will not allow to change the current value. The Oracle sequence can be started with a value by using the START WITH option. The START WITH option can’t be used to modify the current value of the sequence.

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

Error

Error starting at line : 17 in command -
alter sequence mysequence start with 5
Error report -
ORA-02283: cannot alter starting sequence number
02283. 00000 -  "cannot alter starting sequence number"
*Cause:    Self-evident.
*Action:   Don't alter it.


Solution 1

The INCREMENT BY option should be used if you wish to modify the sequence current value to something higher than the current sequence value. When you call the sequence’s NEXTVAL, the INCREMENT BY option will increase the current value to the value specified in it.

After changing the current value to the desired value, the INCREMENT BY option can be reset to its original value. The INCREMENT BY option can be used to modify the sequence’s current value.

create sequence mysequence start with 1 minvalue 1 increment by 1;
-- current value is 1
alter sequence mysequence increment by 10;

select mysequence.nextval from dual;
--sequence current value is 11

alter sequence mysequence increment by 1;
-- reset the increment by 1


Solution 2

If you wish to reset the sequence to a value that is smaller than the current sequence value, you must increase the value by a negative number. The sequence current value will be reduced by the negative increment value.

The sequence INCREMENT BY value should be set with the negative decrement value. If the sequence’s NEXTVAL is called, the sequence’s current value is reduced and reset to the previous value. The value of INCREMENT BY will be reset to its original value.

create sequence mysequence start with 20 minvalue 1 increment by 1;
-- current value is 20
alter sequence mysequence increment by -10;

select mysequence.nextval from dual;
--sequence current value is 10

alter sequence mysequence increment by 1;
-- reset the increment by 1



Leave a Reply