Re: A sequence problem

I think this is what you are looking for:

you want to go from 123 to 10. The difference is 113.

So, "alter sequence sequence_name increment by -113;" Yes that is minus 113.

Now do "select sequence_name.nextval from dual;" this will increment it by -113.

Now do "alter sequence increment by 1;"

Now leave it alone and it is ready for the next person to select from. Just be warned that if this is a primary key and if tries to use a number that is already used, you will get an error. Say of there is a gap between 10- 16, 17 is there and then a gap from 18-123. 11-16 will work, but you will have a problem when it does 17.

John Jones
Senior Oracle DBA
Duke University, OIT
john.jones_at_duke.edu
Anjo Kolk <k.kolk_at_chello.nl> wrote in message news:3AADF3AF.7F0036CF_at_chello.nl...
> Yes you can do that, but it sounds to me that you have a very un
> scalable application. May be it doesn't have to scale, and if that is
> true why not just store a number in a table that you update accordingly.
>
> Anjo.
>
>
> Violin wrote:
>
> > Dear All,
> >
> > If I create a sequence like this :
> > create sequence myseq increment by 1 nocycle nocache;
> >
> > And use it for inserting rows into table:
> > select myseq.nextval into :new.col from dual;
> >
> > And assuming the last number of myseq is 123,
> > May I change the last_number to 10?
> > (for some reason i have to do it ,
> > such like deleting rows from 11th to 123th)
> >
> > Is it possible? and how to?
> >
> > Or I just could do so?
> > drop sequence myseq;
> > create sequence myseq start with 10 increment by 1 nocycle nocache;
> >
> > Because I have lots of sequences , and insert triggers
> > drop and create sequence will cause many INVALID objects.
> >
> > I'm looking for a easy way.
> > Thanks in advance.
> >
> > Best Regards,
> >
> > Violin.
> > violin.hsiao_at_mail.pouchen.com.tw
>
Received on Tue Mar 13 2001 - 14:22:59 CST