Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: How to reset Sequence values ?
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