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: RESETTING A SEQUENCE

Re: RESETTING A SEQUENCE

From: David Fitzjarrell <oratune_at_aol.com>
Date: Mon, 27 Nov 2000 23:59:29 GMT
Message-ID: <8vusgu$j10$1@nnrp1.deja.com>

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

Original text of this message

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