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.
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