How to reset a sequence in Oracle?

How to reset 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 in Oracle?

  • 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.

Databases