Re: Changing the Current Value of a Sequence?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/17
Message-ID: <34c32674.28291200@inet16>#1/1

On Fri, 16 Jan 1998 19:21:07 GMT, grue_at_hotmail.com (Grue) wrote:

>Is there any way to reset the current value of a sequence to a
>different number, other than by dropping and recreating the sequence?
>I know it can't be done using ALTER SEQUENCE. The only thing I can
>come up with is to keep selecting NEXTVAL until I get to the desired
>value, but that seems tedious. Any other ideas?
>

Lets say you wanted a sequence to increment (decrement) by some value N (-N).

You could:

SQL> alter sequence my_seq increment by N;
SQL> select sequence.nextval from dual;
SQL> alter sequence my_seq increment by 1;

that'll do it.... For example:

SQL> select my_seq.nextval from dual;

   NEXTVAL

---

         1

SQL> alter sequence my_seq increment by 100;

Sequence altered.

SQL> select my_seq.nextval from dual;

   NEXTVAL

---

       101

SQL> alter sequence my_seq increment by 1;

Sequence altered.

SQL>   Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 17 1998 - 00:00:00 CST