A few words of thanks would be greatly appreciated.

Resetting a Sequence in Oracle

Latest update on July 21, 2020 at 04:37 AM 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  

Sequence altered.   

SQL> select seq.nextval from dual;   

    NEXTVAL  
----------   
          1   

SQL> alter sequence seq increment by 1;   

Sequence altered.

Reset sequence oracle
Oracle reset sequence - Best answers
How to reset sequence in oracle - Best answers
Reset password oracle - How-To - Oracle
Reset yahoo password without recovery email - How-To - Yahoo mail
How to reset dell laptop - How-To - Laptop computer
Facebook not sending sms code to reset password ✓ - Forum - Facebook
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).

ORA-01630 : max # extents (extent) reached in temp segment

Oracle - Making use of special characters

How To Reset a Sequence

Subscribe To Our Newsletter!