Re: RESETTING A SEQUENCE

> 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.

But you absolutely CAN NOT guarantee that your decrement method of the your sequence will be an atomic transaction not interfered with by other transactions. Your PL/SQL block as you've suggested can easily be stuck in an infinite loop. Your loop's termination point is when the sequence reaches a certain value. Since you can not guarantee that others will not select NEXTVAL, you run the risk of being stuck in your loop forever.

And why would you want to run that risk? Sequences are used for one thing only, generating unique numbers to distinctly denote a business requirement. For instance, sequences are often used to generate purchase order numbers. Let's say that I have a sequence for purchase orders called PO_SEQ. If I query PO_SEQ.CURRVAL, I get '1000'. If I reset this back to '1' using your method, then I can have someone query PO_SEQ.NEXTVAL before I "re-check at the end". Now the PO_SEQ.NEXTVAL gives me '2'. If I decrement to '1' again and another transactions selects NEXTVAL, then I now have two purchase orders with the number '2'. This is definitely not intended.

The one and only way to guarantee that transactions do not modify the sequence while you are resetting it is do drop the sequence and recreate it. And it's quick and easy!

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!
========================================