patternModerate
Change LAST_NUMBER value in a sequence (NEXTVAL)
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.
or
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 workSolution
Change increment by, select next value, set increment back to original value:
Or use
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
-----------
62Or 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
-----------
77Code 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
-----------
62SQL> alter sequence s1 restart start with 77;
Sequence altered.
SQL> select last_number from user_sequences where sequence_name = 'S1';
LAST_NUMBER
-----------
77Context
StackExchange Database Administrators Q#239966, answer score: 11
Revisions (0)
No revisions yet.