If you are running a version of Oracle that is prior to 12.1, there’s no “easy” way to set a sequence value.
This changes in Oracle 12.1 though; you can simply issue the ALTER SEQUENCE .. RESTART command to set a sequence value, like this:
alter sequence seq_owner.seq_name restart minvalue 9999;If you’re not sure which version of Oracle you have, you can either select from V$VERSION. Or, alternatively, you can just use the script below, which should work no matter what version of Oracle you’re on.
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.
Script to Set a Sequence Value
While the intention of the below script is to offer a quick and easy way to set a sequence value, there are no warranties with the below script, expressed or implied. Run this code at your own risk.
<<<set_sequence>>
declare
CONST_SEQUENCE_OWNER varchar2(128) := 'SEQUENCE_OWNER_GOES_HERE';
CONST_SEQUENCE_NAME varchar2(128) := 'SEQUENCE_NAME_GOES_HERE';
CONST_SET_SEQUENCE_TO number := 9999;
v_current_sequence_value number;
v_temp_increment_by number;
v_original_increment_by number;
v_sql_command varchar2(3000);
begin
$if dbms_db_version.version >= 12 $then
-- Things are simple in 12.1 and later
v_sql_command := 'alter sequence ' || CONST_SEQUENCE_OWNER || '.' || CONST_SEQUENCE_NAME || ' restart minvalue ' || CONST_SET_SEQUENCE_TO;
dbms_output.put_line('Issuing command: [' || v_sql_command || ']');
execute immediate v_sql_command;
v_current_sequence_value := CONST_SET_SEQUENCE_TO;
$else
-- If we're running an earlier version of the database, things get trickier.
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;
$end
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;
/The above script works by taking one of two different approaches: If the script is being run in Oracle 12.1 or later, it uses the simple ALTER SEQUENCE…RESTART MINVALUE… command to set the sequence value. And if the script is being run in Oracle in a version prior to 12c, it uses the “old” way of finding out the current value of the sequence, the intended target for the sequence, does some math, sets the INCREMENT BY to the difference of the two numbers, selects from the sequence to bring the sequence to the target number, and then resets the “increment by” of the sequence back to what it originally was.
Hopefully you find this useful!
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
