Home -> Usenet -> comp.databases.oracle -> Re: Resetting a SEQUENCE?

Re: Resetting a SEQUENCE?

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 6 May 2003 16:34:09 -0700
Message-ID: <130ba93a.0305061534.5f8c907_at_posting.google.com> 







j.mccallen_at_lse.ac.uk (jmccallen) wrote in message news:<716895d3.0305060814.2b431e10_at_posting.google.com>...


> We have a sequence on our Oracle 8i database which is working

> perfectly as:

> 

> CREATE SEQUENCE ADV_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 CACHE


> 20 NOCYCLE  NOORDER ;


> 

> Our problem is a dodgy program which has been selecting from the

> sequence unnecessarily, so using up thousands of sequence values which

> were never actually used as the primary key in the table.   As a

> result of this, our users are unhappy about the imminent progression

> to a seven digit id number rather than the standard six digit one.

> 

> Does anyone know if it's possible to alter the sequence to reset it

> back to the original START WITH value and select the next value which

> NOT EXISTS in the table in question?

> 

> e.g. if a listing of the primary key from the table looks like this:

> 1

> 2

> 3

> 6

> 7

> 9

> Can i get the sequence to provide me with a next value of 4, then 5,


> then 8, etc?






You can manipulate "increment" to get your sequences back to the
desired valuse, like this:



SQL> select seq1.nextval from dual;



   NEXTVAL





        41



SQL> alter sequence seq1 increment by -40;



Sequence altered.



SQL> select seq1.nextval from dual;



   NEXTVAL





         1



SQL> /



select seq1.nextval from dual


       *


ERROR at line 1:


ORA-08004: sequence SEQ1.NEXTVAL goes below MINVALUE and cannot be
instantiated




SQL> alter sequence seq1 increment by 1;



Sequence altered.



SQL> select seq1.nextval from dual;



   NEXTVAL





         2



SQL> 





    
  
  •   Jusung Yang



Received on Wed May 07 2003 - 01:34:09 CEST












Original text of this message