Re: Sequences

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/05
Message-ID: <34121d7d.6531792@newshost>#1/1

On Fri, 05 Sep 1997 08:46:07 -0500, Ed Gillispie <eg76440_at_deere.com> wrote:

>Does any one know of a way that you can alter the starting number of a
>sequence with out dropping the sequence.

You can do it without dropping the sequence, consider:

SQL> select myseq.nextval from dual;  

   NEXTVAL

---

       221  

SQL> alter sequence myseq increment by -221 minvalue 0;  

Sequence altered.  

SQL> select myseq.nextval from dual;  

   NEXTVAL

---

         0  

SQL> alter sequence myseq increment by 1;  

Sequence altered.  

SQL> select myseq.nextval from dual;  

   NEXTVAL

---

         1  

SQL> Set the INCREMENT to be negative NEXTVAL, select it once and that'll reset it to zero, alter it to increment by 1 and the next select will get the original value.

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 Received on Fri Sep 05 1997 - 00:00:00 CDT