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

_____________________________________________________________________________________________________________________

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.

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google