Re: RESETTING A SEQUENCE

In our last gripping episode "John Jones" <john.jones_at_duke.edu> wrote:
> 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
> >
> >
> >
>
>

Taking the long way around the lake with that one, I suspect. I usually create public synonyms and grant select to public for application-level sequences; I find no need to selectively grant SELECT privilege on a sequence. With that in mind the recreation of the sequence with the proper starting value is far easier than your somewhate convoluted method.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.