Re: How to reset Sequence values ?

In article <3A71881D.2F0A1BC1_at_edcmail.cr.usgs.gov>,   Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> I know that this has been discussed many months ago in one of these
> newsgroups, but I'll rehash what I wrote back then....
>
> You can employ your strategy of "backdating". And it may work most of
> the time. But the problem is that you cannot guarantee atomicity of
 your
> procedure. Using your example...
>
> > Look at this example:SQL>select test_seq.nextval from dual;
> >
> > NEXTVAL
> > ----------
> > 6
>
> What happens if someone else selects NEXTVAL before you issue your
 ALTER
> SEQUENCE? Then it will be '7' not '6' and your increment should be '-
 6'
> not '-5'.
>
> > SQL>alter sequence test_seq increment by -5;
> >
> > Sekvens er ændret.
>
> What happens if someone else selects NEXTVAL here? Then your sequence
> may be at '-4'!
>
> You simply cannot guarantee atomicity of your procedure to ensure that
> you reset the sequence to your desired value, i.e you can not ensure
> that no one else will select NEXTVAL from the sequence before your
> procedure is finished.
>
> 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!
> ========================================
>

Hi,

And thanks for the interesting input.

In some cases you might be able to rename the sequence before altering it, and back-name it when done. This would have many consequences like invalidating procedures and package bodies, generate runtime errors etc., but it would guarantee atomicity, wouldn't it?

/Michael

Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 01 2001 - 04:03:44 CST