Resetting Oracle Sequence Generators
It isn't very often that you need to reset a sequence generator in an Oracle database.
But the occasion does arise from time to time. Depending on the situation, it may be necessary to either increase the NEXT value of the sequence, or decrease it.
For instance, a few months ago I was working on a project that was in user acceptance. The data in the user acceptance database would be turned over to the users to determine if they were comfortable with the application, and they were allowed to perform transactions on the database.
This was done for several groups over a period of time, and after each group was finished, the data needed to be refreshed for the next group to use.
This was often done by exporting the data from the development database and importing it into the user acceptance database. New data would have been added by the developers that they wanted the next group of users to see.
Here's the catch; the primary keys for most tables are generated via triggers and sequence generators. When importing data to tables that have their primary keys generated this way, it is best to drop the sequences at the same time the tables are truncated.
When the data from the development database is imported, the PK value for many tables may be greater than the NEXT values of the sequence generators in the user acceptance database.
What happens if you don't drop the sequences before importing the data?
The data will import without error, but as soon as someone tries to create a new row in a table, you are likely to get and ORA-0001, 'unique constraint violated'.
This has happened to me. The users are waiting, the developers and project manager are anxious, what do you do?
You could drop and recreate the sequences with the values from the development database. This can be problematic however, as numerous modules of stored code would then be invalidated, and need to be recompiled. While most of these were triggers, which compile fairly fast, a number of the stored procedures also referenced these sequences, and recompiling a large number of these can take quite a while.
Here's where some creative use of SQL*Plus comes to play. It is possible to alter the INCREMENT value of a sequence to a value that is the difference between the current maximum value of the target primary key and current value of the sequence.
By using the NEW_VALUE option of the SQL*Plus COLUMN command, you can assign this value to a SQL*Plus substitution variable.
This value can then be used to increment the sequence to a value greater than the target primary key value. The sequence generator is then set back to it's normal increment value.
An example of doing this can be seen in positive_reset_sequence.sql.
What about resetting a sequence to a lower value?
This can be done also. Before giving an explanation, you may be wondering why anyone would want or need to do that.
While there may be other justifications for doing this, the reason I have used it is due to user requirements.
The requirement was for a document producing system. The documents in this system were identified by the date and and ID number.
This ID was to be generated sequentially, starting with the number one each day.
So if fifty-seven documents were produced on one day, the numbering the next day would again start with one.
Gaps in the numbering sequence were unimportant, but it did have to start again with document number one each day.
My goal was to make this fairly foolproof, and provide a simple procedure call for the developers to make from their Visual Basic forms.
This was accomplished by embedding dynamic SQL via DMBS_SQL in a stored procedure that would manipulate the sequence. If the date had changed since the last document had been created, a lock was taken out via DBMS_LOCK, the sequence generator was reset to zero via DBMS_SQL and an ALTER SEQUENCE statment, the lock released, and the next sequence number ( 1 ) returned.
If the date had not changed since the last document was created, it was still the same day, and the next seqeunce number was simply returned.
Again, this was a case where the sequence could not be dropped.
An example of resetting the increment value of a sequence to a negative number and using that to reset the sequence to start with zero is included below in negative_reset_sequence.sql.
Resetting sequence generators in Oracle is not something you will do very often. When it needs to be done though, it's good to have the right tools in your toolbox.
-------- positive_reset_sequence.sql -------------
-- positive_reset_sequence.sql
-- reset a sequence without dropping it
-- this example is a positive increment
drop table seqtest;
drop sequence seqtest_seq;
create table seqtest ( PK integer not null );
insert into seqtest values(1000);
create sequence seqtest_seq start with 500;
-- get the current value of sequence
-- this is actually the NEXT value,
-- but that doesn't matter, as it
-- will be changed downstream anyway
col cSeqVal noprint new_value uSeqVal
select seqtest_seq.nextval cSeqVal from dual;
-- display old sequence value
select &uSeqVal "OLD SEQUENCE VALUE" from dual;
-- get the current INCREMENT value for the sequence
col cIncByVal noprint new_value uIncByVal
select increment_by cIncByVal
from dba_sequences
where sequence_name = 'SEQTEST_SEQ';
-- get the maximum value of the PK
-- and store in the variable umaxval
col cMaxVal noprint new_value uMaxVal
select max(PK) cMaxVal from seqtest;
-- determine the amount by which the
-- sequence must be incremented
col cIncVal noprint new_value uIncVal
select &uMaxVal - &uSeqVal cIncVal from dual;
-- change the increment value of the sequence
alter sequence seqtest_seq increment by &uIncVal;
-- increment the sequence
select seqtest_seq.nextval "RESETTING SEQUENCE VALUE" from dual;
-- set it back
alter sequence seqtest_seq increment by &uIncByVal;
-- show value of next sequence
select seqtest_seq.nextval "NEW SEQUENCE VALUE" from dual;
-------- negative_reset_sequence.sql -------------
-- negative_reset_sequence.sql
-- reset a sequence without dropping it
-- this example is a negative increment,
-- and will assume that when the sequence
-- is reset, the NEXT value will be 1
drop sequence seqtest_seq;
col cSeqStart noprint new_value uSeqStart
-- choose a somewhat random number
select to_number(to_char(sysdate,'ss')) * to_number(to_char(sysdate,'mi')) cSeqStart
from dual
/
create sequence seqtest_seq start with &uSeqStart;
-- get the current value of sequence
-- this is actually the NEXT value,
-- but that doesn't matter, as it
-- will be changed downstream anyway
col cResetIncVaL noprint new_value uResetIncVaL
select -1 - seqtest_seq.nextval cResetIncVaL from dual;
-- get the current INCREMENT value for the sequence
col cIncByVal noprint new_value uIncByVal
select increment_by cIncByVal
from dba_sequences
where sequence_name = 'SEQTEST_SEQ';
-- do a select on the sequence to display the
-- NEXT value as it is now
select seqtest_seq.nextval "OLD SEQUENCE VALUE" from dual;
-- change the increment value of the sequence
-- notice that the minvalue is set to 0, so that
-- the next use of the sequence will return a 1
alter sequence seqtest_seq
increment by &uResetIncVaL
minvalue 0
/
-- increment the sequence to set it to 0
select seqtest_seq.nextval "RESETTING SEQUENCE VALUE" from dual;
-- set it back
alter sequence seqtest_seq increment by &uIncByVal;
-- display the sequence value to show
-- that the next access will return a 1
select seqtest_seq.nextval "NEW SEQUENCE VALUE " from dual;
--------------------------------------------------