You might expect there to be an easy “alter sequence” command to set a sequence to a certain value, but I don’t know of one.
Today I ran into a situation where I needed to set a sequence to a specific value, so I decided to write a script that would do the job for me.
To use the below script, you only need to plug in three values: The schema that owns the sequence, the name of the sequence, and the value you want to assign the sequence to. Whatever number you use in the script below will actually result in the next sequence.nextval value being that number. So if you have a sequence named MYSEQUENCE that increments by 1, and you use the below script to set it to 9999, after you run the script the next time you run MYSEQUENCE.nextval, it will generate the number 9999.
There are no warranties with the below script, expressed or implied. Run this code at your own risk.
<<set_sequence_nextval_to>>
declare
CONST_SEQUENCE_OWNER varchar2(128) := 'INSERT_SEQUENCE_OWNER_HERE';
CONST_SEQUENCE_NAME varchar2(128) := 'INSERT_SEQUENCE_NAME_HERE';
CONST_SET_SEQUENCE_TO number := 9999;
v_current_sequence_value number;
v_temp_increment_by number;
v_original_increment_by number;
begin
execute immediate 'select ' || CONST_SEQUENCE_OWNER || '.' || CONST_SEQUENCE_NAME || '.nextval from dual' into v_current_sequence_value;
select ds.increment_by into v_original_increment_by
from dba_sequences ds
where upper(ds.sequence_owner) = upper(set_sequence_nextval_to.CONST_SEQUENCE_OWNER)
and upper(ds.sequence_name) = upper(set_sequence_nextval_to.CONST_SEQUENCE_NAME);
v_temp_increment_by := (CONST_SET_SEQUENCE_TO - v_current_sequence_value) - v_original_increment_by;
execute immediate 'alter sequence ' || CONST_SEQUENCE_OWNER || '.' || CONST_SEQUENCE_NAME || ' increment by ' || v_temp_increment_by;
execute immediate 'select ' || CONST_SEQUENCE_OWNER || '.' || CONST_SEQUENCE_NAME || '.nextval from dual' into v_current_sequence_value;
execute immediate 'alter sequence ' || CONST_SEQUENCE_OWNER || '.' || CONST_SEQUENCE_NAME || ' increment by ' || v_original_increment_by;
dbms_output.put_line('Sequence ' || CONST_SEQUENCE_OWNER || '.' || CONST_SEQUENCE_NAME || ' is currently set to ' || v_current_sequence_value || '.');
dbms_output.put_line('Next time someone issues:' || chr(10));
dbms_output.put_line(' select ' || CONST_SEQUENCE_OWNER || '.' || CONST_SEQUENCE_NAME || '.nextval');
dbms_output.put_line(' from dual;' || chr(10));
dbms_output.put_line('they will get ' || CONST_SET_SEQUENCE_TO || '.');
end set_sequence_nextval_to;
/Free Oracle SQL Tuning Guide
Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!
Get it here: tuningsql.com/secrets
