Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: RESETTING A SEQUENCE
In our last gripping episode yong321_at_yahoo.com wrote:
> Hi, Brian,
>
> In addition to drop and recreate it, John can simply modify his way to
> do it. Store the number in a PL/SQL variable, decrement it by that
> number, re-check at the end. If the number is incremented (by somebody
> else in between), loop back and do it again. This time the decrement
> number will be different for sure. Then re-check.
>
> Yong Huang
> yong321_at_yahoo.com
>
> In article <3A2280C0.5A0757CD_at_edcmail.cr.usgs.gov>,
> Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> > John,
> >
> > I have to disagree with your procedure here. You have no way of
> > guaranteeing automicity for your transaction. What happens if
someone
> > selects NEXTVAL from the sequence in the middle of your procedure?
> >
> > > First find out what your sequence is:
> > > "select sequence_name.nextval from dual;" let's say that gives
you
1050 and
> > > you want to reset to 1000.
> > >
> > > next "alter seqeunce sequence_name increment by -50""
> > > this just resets the increment value.
> >
> > If another user now selects NEXTVAL before you issue yours, your
> > sequence will then be decremented by 100.
> >
> > > then "select sequence_name.nextval from dual;" this gives you the
number
> > > 1000.
> > >
> > > next "alter sequence sequence_name increment by 1;" do not select
from it
> > > now, and it will be ready to give you the next number of 1001 the
next time
> > > somebody selects from it.
> >
> > What if the user selects NEXTVAL before you get a chance to change
the
> > increment value?
> >
> > The only way to ensure that a sequence gets reset to some value is
to
> > drop it and recreate it. If you have granted privileges to other
users
> > for that sequence, then you'll have to re-grant them. This isn't a
big
> > deal since you can always create a script.
> >
> > HTH,
> > Brian
> >
> > --
> > ========================================
> > Brian Peasland
> > Raytheons Systems at
> > USGS EROS Data Center
> > These opinions are my own and do not
> > necessarily reflect the opinions of my
> > company!
> > ========================================
> >
>
> --
> Yong Huang
>
> (yong321_at_yahoo.com)
> (http://www.stormloader.com/yonghuang/)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Still a bit more work than necessary for the task at hand. For the most reliable results drop the sequence and recreate it with the desired starting value.
-- David Fitzjarrell Oracle Certified DBA Sent via Deja.com http://www.deja.com/ Before you buy.Received on Mon Nov 27 2000 - 17:59:29 CST