HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

Change LAST_NUMBER value in a sequence (NEXTVAL)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
valuenextvalsequencelast_numberchange

Problem

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

Solution

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

Code Snippets

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
SQL> alter sequence s1 restart start with 77;

Sequence altered.

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

LAST_NUMBER
-----------
         77

Context

StackExchange Database Administrators Q#239966, answer score: 11

Revisions (0)

No revisions yet.