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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google