Re: Unique ID's question

John Jones <jmj22026_at_us1j69.glaxo.com> wrote:
>>To reset sequence numbers drop and re-create sequence.
>>To get more information of sequences query data dictionary view USER_SEQUENCES
>
>alter sequence sequence_name increment by -99;
>select sequence_name.nextval from dual; 'resets the number to 1'
>alter sequence sequence_name increment by 1;
>
>the next time you select on the sequence it will be '2'. Much easier than
>re-creating and then having to re-grant the rights.

You have to be careful nobody else will try to access the sequence while you do this, otherwise some poor user might find themselves with an order reference of -97!

Recreating is ok if you have the creation and grants held conveniently in a script, or if you have a script that can generate the creation and grant statements before you drop the sequence.

-- 
** Mark Styles aka Small       -- Opinions expressed here are my own --   **
** marks_at_teleord.co.uk         -- unless otherwise specified         --   **
**           This whole world's wild at heart and weird on top            **