A few words of thanks would be greatly appreciated.
Resetting a Sequence in Oracle
Latest update on by David Webb
.
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
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).
