Re: How to reset Sequence values ?

Doug,

While you managed to update the USER_SEQUENCES view, you did not effect the sequence. See my example below:

SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
TEST_SEQ                                10

NTW57 SQL> select test_seq.nextval from dual;

   NEXTVAL

        10

NTW57 SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
TEST_SEQ                                11

NTW57 SQL> update user_sequences set last_number='50' where sequence_name='TEST_SEQ';

1 row updated.

NTW57 SQL> select sequence_name,last_number from user_sequences;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
TEST_SEQ                                50

NTW57 SQL> select test_seq.nextval from dual;

   NEXTVAL

        11

While the LAST_NUMBER was executed, I still got '11' as my NEXTVAL!!!!

There are only two ways to reset a sequence (and only one is *guaranteed* to work).
1) Drop & recreate the sequence
2) have the sequence cycle back to it's original value and select NEXTVAL enough times to cycle around.

But option 2 is not guaranteed if someone else selects NEXTVAL during your procedure.

HTH,
Brian

Doug O'Leary wrote:
>
> Hey;
>
> SQL> select sequence_name, last_number
> 2 from user_sequences;
>
> SEQUENCE_NAME LAST_NUMBER
> ------------------------------ -----------
> HRS 22
> KEYS 90
>
> SQL> update user_sequences
> 2 set last_number = 1
> 3 where sequence_name = 'KEYS';
>
> 1 row updated.
>
> SQL> select sequence_name, last_number
> 2 from user_sequences;
>
> SEQUENCE_NAME LAST_NUMBER
> ------------------------------ -----------
> HRS 22
> KEYS 1
>
> I then created a table and used the sequence; seemed to work. Am I
> missing something?
>
> Doug
>
> --
> -------------------
> Douglas K. O'Leary
> Senior System Admin
> dkoleary_at_mediaone.net
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================