Re: Resetting a SEQUENCE?

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0305061534.5f8c907_at_posting.google.com>...
> j.mccallen_at_lse.ac.uk (jmccallen) wrote in message news:<716895d3.0305060814.2b431e10@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

Jusung, nice demo, but my reading of JM's post is that he wants to set the sequence back AND have it automatically skip or recognize used numbers in the target table. There is no native feature to do this.

I would suggest looking at resequencing the data, but depending on how many related tables there are and the age of the data this is probably not possible from a business point of view.

You can look at changing the PK to be the sequence number plus the date; that way the same numbers could be reused without worring about duplicates, but again this is going to require significant application review, change, and testing.

You could replace the use of the Oracle nextval function with a user function that looks at a table of missed values and selects the lowest unused value and deletes it within an anonymous transaction. Once the table is empty the logic would just call the Oracle sequence as normal and pass the value back. You would have to change the code everywhere the sequence is invoked, but that is better than trying to change the design.

HTH -- Mark D Powell -- Received on Wed May 07 2003 - 15:32:53 CEST