1
Thanks

A few words of thanks would be greatly appreciated.

Oracle - Resetting a sequence

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
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Published by . Latest update on by Ratnendra Ashok.

This document, titled "Oracle - Resetting a sequence," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).

0 Comments