Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: about sequence in oracle
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 - 01:01:07 CDT