Reset a Sequence with Original NEXTVAL
There're two way to reset sequence to exact number you want in Oracle:
1. Start Sequence to Exact Number
Let's say someone abused a sequence object and you found the current sequence number is far away from the current logic. What should we do? Can we reset the sequence number to the point before being abused?
Here we drop it and create the sequence with the same name.
SQL> conn sh/sh
Connected.
SQL> drop sequence cust_no;
Sequence dropped.
SQL> create sequence cust_no start with 55501;
Sequence created.
Of course, you can also ignore START WITH clause to make it start with 1 if there's no MINVALUE specified. Which is the default behavior.
For newly created sequence object, you might see errors like ORA-08002: sequence CURRVAL is not yet defined in this session.
2. Roll Sequence to Exact Number
If you don't know the exact number that you have, you can also roll the sequence to the exact number you need.
SQL> drop sequence cust_no;
Sequence dropped.
SQL> create sequence cust_no;
Sequence created.
SQL> set pagesize 0;
SQL> select cust_no.nextval from customers;
1
2
3
4
5
6
7
8
9
...
55499
55500
55500 rows selected.
SQL> select cust_no.currval from dual;
55500
That is, the next value of the sequence will be 55501.