Re: about sequence in oracle

Hi David,

I see that Thomas Kyte has posted a reference to the sort of manipulation I was thinking, so thanks to him... I agree I was too blazé about explaining the exact procedures (but I was rather more worried about why gaps in a sequence would be a problem in the first place).

Incidentally, I don't recall seeing anything to suggest that the technique has changed in 9i (though I may have nodded off at the crucial moment, it is true).

Regards
HJR "David Fitzjarrell" <oratune_at_msn.com> wrote in message news:32d39fb1.0108130704.383b5168_at_posting.google.com...
> 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 Tue Aug 14 2001 - 05:10:40 CDT