1
Thanks

A few words of thanks would be greatly appreciated.

Resetting a Sequence in Oracle

It is possible to reset a sequence in Oracle. The company does not provide a ready-made command for this task. However, it is possible to reset it by following the instructions below.

How To Reset a Sequence

To reset a sequence SEQ created through the command:

CREATE SEQUENCE seq;

, search for its current value via the command:

SEQ.CURRVAL SELECT FROM DUAL;

Then, change the sequence by adding the option and adding increment value VAL(current value - 1) to reset the sequence to 1:

ALTER SEQUENCE SEQ INCREMENT by -VAL;

Then run the command that will reset the sequence:

SEQ.NEXTVAL SELECT FROM DUAL;

To restore the increment of the sequence:

ALTER SEQUENCE SEQ INCREMENT by 1; 

Example:

SQL> create sequence seq;   
Sequence created.
SQL> select seq.nextval from dual;
NEXTVAL
----------
1
SQL> select seq.nextval from dual;
NEXTVAL
----------
2
SQL> select seq.nextval from dual;
NEXTVAL
----------
3
SQL> select seq.currval from dual;
CURRVAL
----------
3
SQL> alter sequence seq increment by -2; // 2=SEQ.CURRVAL-1
Sequence altered.
SQL> select seq.nextval from dual;
NEXTVAL
----------
1
SQL> alter sequence seq increment by 1;
Sequence altered.

Image: © JPStock - Shutterstock.com

1
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
Related
This document, titled « Resetting a Sequence in Oracle », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).