Oracle – Change LAST_NUMBER Value in a Sequence (NEXTVAL)

oraclesequence

My question seems easy but i haven't found a solution.
I'm using the nexval sequence in a insert SQL script but we have errors because the LAST_NUMBER value haven't been updated and its value is not correct with tabla data. For this case for example the value is 5 and the table has 62 rows.

I've searched on google but don't found the command or the way yo change the LAST_NUMBER parameter of the sequence.

ALTER SEQUENCE rs2qnes0 START WITH 62;

or ALTER SEQUENCE rs2qnes0 LAST_NUMBER 62; don't work

Best Answer

Change increment by, select next value, set increment back to original value:

SQL> create sequence s1 start with 5;

Sequence created.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
          5

SQL> alter sequence s1 increment by 57;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
        61

SQL> alter sequence s1 increment by 1;

Sequence altered.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
         62

Or use RESTART:

SQL> alter sequence s1 restart start with 77;

Sequence altered.

SQL> select last_number from user_sequences where sequence_name = 'S1';

LAST_NUMBER
-----------
         77
Related Question