Re: about sequence in oracle

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.

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.
Received on Mon Aug 13 2001 - 10:04:55 CDT