Re: RESETTING A SEQUENCE

I beg to differ on this one. You can reset a sequence with an alter command. If you drop and recreate, you will have to remember all of the select privileges that you have granted.

to reset with alter, do the following:

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.

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.

John Jones
Senior Oracle DBA
Duke University, OIT
john.jones_at_duke.edu
Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:8vtlq4$5qnut$2_at_ID-62141.news.dfncis.de...
>
> "SDG" <simong_at_explor.co.uk> wrote in message
> news:7psU5.276$4o3.168024_at_newsr1.u-net.net...
> > HOW DO YOU RESET A SEQUENCE NUMBER ?
> >
> > Thanks in anticipation
> > SDG
> >
> >
> First of all: please stop using caps.
> You can reset a sequence by dropping and recreating it, alter sequence
 will
> not work.
>
> Hth,
> Sybrand Bakker, Oracle DBA
>
>
>
Received on Mon Nov 27 2000 - 09:08:09 CST