Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: about sequence in oracle

Re: about sequence in oracle

From: David Fitzjarrell <oratune_at_msn.com>
Date: 14 Aug 2001 09:21:56 -0700
Message-ID: <32d39fb1.0108140821.76a1750d@posting.google.com>

Apparently my post was not as clear as it appeared at first blush. My intent was to indicate that there is no START WITH option to ALTER SEQUENCE. Yes, it is true that through judicial use of negative increments a sequence can be brought back to its original starting value, and 'resetting' the value of a sequence from 4 to 1 is a trivial matter indeed. My thrust was to indicate that, even in 8i, there is no such ALTER SEQUENCE ... START WITH ... option. And, for large discrepancies [starting value of 1, current value 15687] it is far easier to drop and recreate the sequence with the proper starting value than it would be to run through 15686 iterations with a negative increment.

I did not intend to state that it was not possible with various ALTER SEQUENCE statements, just that START WITH was not a valid parameter for the command.

David Fitzjarrell
Oracle Certified DBA

"Howard J. Rogers" <howardjr_at_www.com> wrote in message news:<3b78f852_at_usenet.per.paradox.net.au>...
> 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 - 11:21:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US