Here is an example below to restart a sequence in Oracle without dropping it.
Follow These Steps To Restart a Sequence in Oracle
- First, get the next value of the sequence.
SELECT emp_seq.NEXTVAL FROM DUAL;
Example Output:
NEXTVAL ---------- 64 1 row selected.
- The next value of the sequence is 64, so now modify the sequence using ALTER SEQUENCE statement and set the increment value to minus 64 (-64), as shown in the below example:
ALTER SEQUENCE emp_seq INCREMENT BY -64 MINVALUE 0;
Or suppose, you want to restart the sequence from 50 then specify -15. In this case, it will first set the sequence to start from 49 and when you will follow the below steps, it will be ready to start from 50.
Output:
Sequence altered.
- Now select the next value again from the sequence and it will return the 0.
SELECT emp_seq.NEXTVAL FROM DUAL;
Output:
NEXTVAL ---------- 0 1 row selected.
- Again alter the sequence to start with 1, as shown below:
ALTER SEQUENCE emp_seq INCREMENT BY 1 MINVALUE 0;
Output:
Sequence altered.
- Now when you will select the next value it will show the 1 and will continue to generate it by incrementing with 1.
SELECT emp_seq.NEXTVAL FROM DUAL;
Output:
NEXTVAL ---------- 1 1 row selected.
