Re: RESETTING A SEQUENCE

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