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 seqextval from dual;
NEXTVAL
----------
1
SQL> select seqextval from dual;
NEXTVAL
----------
2
SQL> select seqextval from dual;
NEXTVAL
----------
3
SQL> select sequrrval from dual;
CURRVAL
----------
3
SQL> alter sequence seq increment by -2; // 2=SEQ.CURRVAL-1
Sequence altered.
SQL> select seqextval from dual;
NEXTVAL
----------
1
SQL> alter sequence seq increment by 1;
Sequence altered.
Image: © JPStock - Shutterstockom