Systems Engineering and RDBMS

Re-setting Sequence Value

Posted by decipherinfosys on July 26, 2007

In Oracle, sequences are generally used to auto generate column IDs. You can read more about Sequences and their usage in our previous post – here.  In today’s post, we are going to look at how we can re-set the sequence value. Re-setting might be needed when we have used up all the values for a given sequence (A very rare occurence even if you are calling it thousands of time per second) or when someone set the increment value wrongly and you need to correct it.

First let us create an empty table.

CREATE TABLE Test
(
Test_ID NUMBER(9) NOT NULL,
Test_DATE DATE,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)
— TABLESPACE Clause
/

CREATE SEQUENCE TEST_SEQ
/

Now let us populate it with some data using sequence and without using sequence.

INSERT INTO Test(Test_ID,Test_Date) VALUES(Test_seq.nextval,sysdate);
INSERT INTO Test(Test_ID,Test_Date) VALUES(Test_seq.nextval,sysdate);
INSERT INTO Test(Test_ID,Test_Date) VALUES(3,sysdate);
INSERT INTO Test(Test_ID,Test_Date) VALUES(4,sysdate);

Now try to insert another record using sequence.

INSERT INTO Test(Test_ID,Test_Date) VALUES(Test_seq.nextval,sysdate);

It will give (ORA-00001) unique constraint violation error but sequence will be incremented by 1.

Run following steps to obtain correct sequence so that we no longer run into constraint violation. First we will get the difference of max(test_id) and test_seq.currval.

SELECT Test_ID – (test_Seq.currval) + 1
FROM (SELECT Max(Test_ID) Test_ID
FROM TEST)
/

Now alter sequence with the value obtained from the above query(2 in this case) and then we will obtain the next value of the sequence.

SQL> ALTER SEQUENCE test_Seq INCREMENT by 2;
SQL> SELECT test_Seq.nextval FROM dual;

Output of above query will be 5. Since we have incremented it with desired value let us alter sequence again to be incremented by 1 and get the next value so that we get continuous numbers from now on.

SQL> ALTER SEQUENCE test_Seq INCREMENT by 1;
SQL> SELECT test_Seq.nextval FROM dual;

Output of above select statement will be 6. Now let us try to insert some more records.

INSERT INTO Test(Test_ID,Test_Date) VALUES(Test_seq.nextval,sysdate);
INSERT INTO Test(Test_ID,Test_Date) VALUES(Test_seq.nextval,sysdate);

Insert will be successful and we are back on the track. Here is the result set.

SQL> select test_id from test;
TEST_ID
——-
1
2
3
4
7
8

Let us delete records from the TEST table and reset the sequence back to 1. For resetting sequence to 1, we will still alter the sequence by incrementing but this time with negative number, get the next value of the sequence and again alter it to be incremented by 1.

SQL> DELETE FROM TEST;
SQL> ALTER SEQUENCE test_Seq increment by -8 minvalue 0;
SQL> SELECT test_Seq.nextval from dual;
SQL> ALTER SEQUENCE test_Seq increment by 1;

Now insert some more records.

INSERT INTO Test(Test_ID,Test_Date) VALUES(Test_seq.nextval,sysdate);
INSERT INTO Test(Test_ID,Test_Date) VALUES(Test_seq.nextval,sysdate);

Selecting test_id from the Test table will give following results.
TEST_ID
——-
1
2

Altering sequence is better than the dropping and re-creating sequence as dropping sequence will invalidate its dependent objects like procedures and triggers.

2 Responses to “Re-setting Sequence Value”

  1. […] columns in SQL ServerExtracting day, month or year from a date fieldCOALESCE(), ISNULL(), NVL()Re-setting Sequence ValueLinked Servers in SQL ServerBack to the Basics: Difference between Primary Key and Unique […]

  2. […] by decipherinfosys on August 8th, 2007 In some of our previous posts, we had looked at how to re-set the sequence values in Oracle and how to re-seed the identity values for a column in SQL Server. DB2 LUW has the best […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: