Re: ???? Reseting a sequence value ????

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/02
Message-ID: <34fabb0d.1123485@192.86.155.100>#1/1

A copy of this was sent to ilepper_at_fac.fbk.eur.nl (ilepper) (if that email address didn't require changing) On Mon, 2 Mar 1998 09:27:37 +0100, you wrote:

>Is it possible to reset a sequence value back to 1 again?
>So that when SEQ.NextVal will jump to 2.
>
>Anyway thanks,
>
>Ido de Lepper
>ilepper_at_fac.fbk.eur.nl

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
Herndon VA  

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 Mon Mar 02 1998 - 00:00:00 CST