Re: about sequence in oracle

In article <32d39fb1.0108130704.383b5168_at_posting.google.com>, oratune_at_msn.com says...
>
>I hate to say this, Howard, but even with 8i you can't do what you've
>stated. Possibly this is available in 9i, but with all releases up to
>and including 8.1.6 (I don't have the documentation installed for
>8.1.7 so I hesitate to include it in the list, but I doubt that this
>was changed between 8.1.6 and 8.1.7) it is necessary to recreate the
>sequence to change the START WITH value. ALTER SEQUENCE simply will
>not work.
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:951269671592

shows how to reset a sequence using ALTERS without dropping the sequence. The same general method works in all releases

You just need to alter two times -- to twiddle with the increment by (once to get it to go back, once again to reset it be to +1)

>David Fitzjarrell
>Oracle Certified DBA
>
>"Howard J. Rogers" <howardjr_at_www.com> wrote in message
>news:<3b776c52_at_usenet.per.paradox.net.au>...
>> Yes, you can alter the sequence and reset it to be 1 manually.
>>
>> However, if you're telling me that gaps in your sequence are not acceptable
>> (ie, that the sequence number is actually meaningful, and not just a
>> synthetic means of achieving uniqueness) then I'd say that the use of
>> sequences is inappropriate -because you'll find that inserting a new row
>> increments the sequence number, but performing a rollback of that insert
>> won't decrement the sequence. Under normal use, I'd expect a sequence to be
>> riddled with holes and gaps.
>>
>> In the past, when confronted with this problem, I've created a table which
>> holds the next sequence number as a plain old number. An insert into table
>> A also generates an update on table Z set sequA = sequA+1, and a rollback
>> thus undoes both bits of DML. The next insert therefore finds the sequence
>> number at the right value.
>>
>> Regards
>> HJR
>>
>>
>> "Leader" <sohelcsc_at_yahoo.com> wrote in message
>> news:b1a93c73.0108122135.4c9625a7_at_posting.google.com...
>> > Dear all,
>> > i have a problem. that is, suppose i have created sequence for
>> > primary key of one table where start with 1, increment by 1, .... and
>> > i have inserted three rows in the table. after that i delete the rows.
>> > but when i insert next time primary id starts with 4. is there any
>> > options without droping the sequence, if i want to start the id again
>> > from 1.
>> >
>> > thanks,
>> > hoque.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp