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

Home -> Community -> Usenet -> c.d.o.tools -> Re: How to reset Sequence values ?

Re: How to reset Sequence values ?

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: Fri, 26 Jan 2001 14:22:21 GMT
Message-ID: <3A71881D.2F0A1BC1@edcmail.cr.usgs.gov>

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!
========================================
Received on Fri Jan 26 2001 - 08:22:21 CST

Original text of this message

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