Reset Oracle Sequence Value

Many of the Oracle developers are searching the internet for resetting the sequence value.

There is no such attribute to alter the sequence value directly in Oracle.

But a few simple steps will solve the problem.

Let me take an example and explain the steps.

I have a sequence, emp_id_seq, and its current value is 100.

I need to reset the sequence value to 150.

SELECT emp_id_seq.currval FROM DUAL;

O/P: 100

In Oracle, we can alter the sequence increment by value.

With the help of it, alter the increment value such that the next increment should reach your expected value. In our example, I am incrementing by 50 to reach 150 from 100.

ALTER SEQUENCE emp_id_seq INCREMENT BY 50;

Do select the sequence next value to reach the expected value.

SELECT emp_id_seq.nextval FROM DUAL;

O/P: 150

Set back your increment value. If your increment value before is 1, set it back to 1.

ALTER SEQUENCE emp_id_seq INCREMENT BY 1;

Thats it!!!

Its very simple.

From here, your sequence value increments by 1 as usual.

SELECT emp_id_seq.nextval FROM DUAL;

O/P: 151

Note: You can also give negative numbers in the Increment By option. For example, if you need to reset the sequence value from 150 to 100, then give -50 so that it will be decremented by 50.