Re: Help ! - How to resync sequence numbers to a table (Oracle)

From: Gump <stoopidstoopidstoopid_at_hotmail.com>
Date: 16 Feb 2005 15:22:13 -0800
Message-ID: <1108596133.388799.220760@c13g2000cwb.googlegroups.com>

This is one of the most common problems related to sequences and there is no easy solution.

You cannot simply alter the minvalue - see the following example.

SQL> select testseq.nextval from dual;

   NEXTVAL

---

        10

SQL> alter sequence testseq minvalue 10;

Sequence altered.

SQL> alter sequence testseq minvalue 11; alter sequence testseq minvalue 11
*
ERROR at line 1:
ORA-04007: MINVALUE cannot be made to exceed the current value

You have a few options:

1. drop and recreate the sequences with the necessary starting value
2. select nextval from the sequence until it meets the necessary value
3. change the increment by for the sequence to a number large enough to

set the sequence correctly, select nextval, and then change the increment back to what it was, e.g. sequence nextval = 10 and increment by = 1, you need it to be 100, change the increment by = 90, select nextval, then change the increment by back to 1.

I would probably go for option 2. You can write a plsql procedure to determine the value the sequence needs to be set at and then run a loop that will select nextval from the sequence until it meets the necessary value.

If you search around the internet you may find a generic script to do this. But it's not too difficult to write yourself. Received on Wed Feb 16 2005 - 17:22:13 CST