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.
