Reset/Modify/alter Oracle sequence nextval, currval to new value without dropping

advertisements

_____________________________________________________________________________________________________________________

The simplest method to alter the Oracle sequence currval or nextval is drop and recreate the sequence with new “start with” value.

SQL> create sequence seq_example  start with 1001 increment by 1 cache 10;

Sequence created.

SQL> select seq_example.nextval, seq_example.currval from dual;

   NEXTVAL    CURRVAL

---------- ----------

      1001       1001

SQL>  select seq_example.nextval, seq_example.currval from dual;

   NEXTVAL    CURRVAL

---------- ----------

      1002       1002

Suppose if you wanted to reset the sequence to a new value 901, drop it and recreate it.

SQL> create sequence seq_example  start with 901 increment by 1 cache 10;

Sequence created.

SQL> select seq_example.nextval, seq_example.currval from dual;

   NEXTVAL    CURRVAL

---------- ----------

       901        901

Next we will discuss about resetting the sequence without dropping it. It can be achieved with the increment by clause with the sequence.

See the example below.

I am going to create an index start value 901.

SQL> create sequence seq_example  start with 901 increment by 1 cache 10;

Sequence created.

SQL> select seq_example.nextval, seq_example.currval from dual;

   NEXTVAL    CURRVAL

---------- ----------

       901        901

And I have taken some values from the sequence and its current value (currval) is 1436. Now I wanted to reset the sequence current value to 501 (smaller value). See the demo below.

Example 1: Resetting the value to smaller value

Step1. Find out the difference between current value and the value to be reset.

SQL>  select 1436-501 from dual;

  1436-501

----------

       935

Step2. Modify the increment by value of the sequence.

SQL> alter sequence seq_example increment by -935 ;

Sequence altered.

Step3. Fetch a value from sequence

SQL> select seq_example.nextval, seq_example.currval from dual;

   NEXTVAL    CURRVAL

---------- ----------

       501        501

Step4. Reset the increment by to the original value (here it is 1)

SQL> alter sequence seq_example increment by 1;

Sequence altered.

Example 2: Resetting the value to higher value

Here, my sequence current value is 501 and I wanted to reset it to 2301.

Step1.

SQL> select 2301-501 from dual;

  2301-501

----------

      1800

Step2.

SQL> alter sequence seq_example increment by 1800;

Sequence altered.

Step3.

SQL> select seq_example.nextval, seq_example.currval from dual;

   NEXTVAL    CURRVAL

---------- ----------

      2301       2301

Step4.

SQL> alter sequence seq_example increment by 1;

Sequence altered.

_____________________________________________________________________________________________________________________

Website Stats