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
Related
- Reset sequence oracle
- Oracle reset sequence - Best answers
- How to reset sequence in oracle - Best answers
- How to reset the oracle database password - How-To - Oracle
- How to reset samsung tv - How-To - Hardware
- How to factory reset dell laptop - How-To - Laptop computer
- How to reset browser on android - How-To - Android
- Samsung reset code - How-To - Samsung
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).
