Tech Blog‎ > ‎

Oracle ALTER SEQUENCE - START WITH not working?

posted Feb 17, 2011, 10:57 AM by Victor Zakharov   [ updated Mar 30, 2011, 7:58 AM ]
I googled over many websites explaining the error code and suggesting you do not change it. What if you really need to?

One approach is to call NEXTVAL as many times to shift the sequence, so that it starts with your START WITH number (<SW>).
A better one is to change INCREMENT BY to a certain value instead, then do NEXTVAL and revert INCREMENT BY.

For example, suppose you currently have an increment = <I>, your current sequence value is <C>.
Assuming sequence name is 'seq', the query would look similar to the following:

ALTER SEQUENCE seq INCREMENT BY <SW> - <C>;
SELECT seq.NEXTVAL FROM dual;
ALTER SEQUENCE seq INCREMENT BY <I>;

Same approach works for resetting a sequence, you just need to use negative increments. 
See discussion on stackoverflow for more options.
Comments