Re: A sequence problem

On Tue, 13 Mar 2001 15:22:59 -0500, "John Jones" <john.jones_at_duke.edu> wrote:

Hello Anjo,

It's really such a good idea!

Thank you very much.

Violin.
violin.hsiao_at_mail.pouchen.com.tw

>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 - 18:06:59 CST