Oracle Database Discussions

Hi Guys,

I have an issue resetting a sequence, and wondering if any of you have any ideas how to get this to work.

DROP SEQUENCE ABC_SEQ;

Sequence dropped.

CREATE SEQUENCE ABC_SEQ MINVALUE 1 START WITH 1 INCREMENT BY 1;

Sequence created.

SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 1 1 1

Now this is the important bit, as expected the first time I select from the sequence I get the value 1.

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
1

SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 1 1 21

Now if i generate a few more numbers

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
2

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
3

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
4

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
5

Now I want to reset the sequence back to 1, so i go through the normal process of using a negative increment

SQL> ALTER SEQUENCE ABC_SEQ INCREMENT BY -4;

Sequence altered.

SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 1 1 21

SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 1 -4 1

I then have to actually select the nextval to make it work

SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
1

SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 1 -4 -3

And then change the increment back, so that the application will be able to use the start number.

ALTER SEQUENCE ABC_SEQ INCREMENT BY 1;

Sequence altered.

SQL> SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 1 1 2

So now my problem is when the application uses the sequence it will get 2 not 1, and i want it to be 1.

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
2

So then i started playing with temporarily setting the min value to 0 so the nextvalue selection via the
reset process will be 1, but then you have to wait until after the application selects the nextval before
setting it back :

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
3

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
4

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
5

ALTER SEQUENCE ABC_SEQ INCREMENT BY -5 MINVALUE 0;

Sequence altered.

SQL> SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 0 -5 0

SQL> SELECT ABC_SEQ.NEXTVAL FROM DUAL;

NEXTVAL
----------
0

SQL> SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 0 -5 -5

ALTER SEQUENCE ABC_SEQ INCREMENT BY 1;

SQL> SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'ABC_SEQ';

SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ------------ -----------
ABC_SEQ 0 1 1

So now the next time the application uses the sequence i will get 1, which is what i want, but the min_value was not
what it originally was. Now if i try change the min_value now, i get an expected error :

ALTER SEQUENCE ABC_SEQ MINVALUE 1
*
ERROR at line 1:
ORA-04007: MINVALUE cannot be made to exceed the current value

so now i have to wait until something selects the next value (and that should be the application so it gets to use number 1),
but cannot predict when that will be, so i haven't actually been able to reset it, its all an approximation.

Sorry if i've missed something obvious, but have been pulling my hair out on this one.

Any help is approciated (Please note that a solution is not to just start at 2 (it's not up to me the application needs it) or leave the min value at 0 (because then i've changed it), the only way i can really reset this is to drop an recreate it, which is something i would perfer to avoid if possible).