• Post author:
  • Post category:SQL
  • Reading time:2 mins read

Here is an example below to restart a sequence in Oracle without dropping it.

Follow These Steps To Restart a Sequence in Oracle

  1. First, get the next value of the sequence.
SELECT emp_seq.NEXTVAL FROM DUAL;

Example Output:

NEXTVAL
----------
64
1 row selected.
  1. 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.
  1. 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.
  1. Again alter the sequence to start with 1, as shown below:
ALTER SEQUENCE emp_seq INCREMENT BY 1 MINVALUE 0;

Output:

Sequence altered.
  1. 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.

See also:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.