Thanks to a recent product upgrade a sequence in one of our databases was reset to about 100,000 below its previous value. To reset it Oracle, and most other sites, tell me I need to drop the sequence and recreate it to change its current value.
To avoid dropping the sequence and invalidating all the triggers and anything else that is dependent on it I decided a different approach was in order.
I first determined the difference between the trigger and the maximum value in the table. I then changed the amount it increments with to the difference (plus a few to be save), selected nextval, then change it back to increment by 1.
The commands were something like this:
alter sequence id_sequence increment by 142900;
select id_sequence.nextval from dual;
alter sequence id_sequence increment by 1;
This method of course only works to increase the sequence. One additional risk is that something will increment the trigger while the increment is set high. In this case you’re stuck dropping the sequence and recreating it. Just remember, if it comes to this you’ll want to recompile all invalid objects so they won’t slow down the next time they run.
You can specify a negative increment to decrease the sequence.
alter sequence id_sequence increment by -142900;
More info:
http://www.jlcomp.demon.co.uk/faq/resetseq.html
Thanks ko! This is a great tip!
This was very helpful! Your site is great.
Thanks, it was correctly mentioned.
thanks a lot
nice work, but how would you make this into a procedure so that you could pass in the sequence name and the value you want it to be changed to?
Not a bad idea Kevin, but this is something I hope people aren’t doing too often so I don’t consider it a good candidate for a procedure. Sequences aren’t really intended to be reset all the time and if you find you need to you might want to consider using something other than a sequence.
I have a situation where using a procedure may be beneficial:
We have development databases for all of our developers. Each developer gets a sandbox to play in. Some of them play with more sand than others, so their sequence numbers are higher. We have a base data set that gets updated periodically that the developers use to refresh their data periodically to a known, good state. This, unfortunately, results in occasional tromping of sequence numbers. I have tried to build a procedure to step them, but it still does not work.
declare
sqlstmt varchar2(200);
begin
for x in (select sequence_name from user_sequences) loop
sqlstmt := ‘alter sequence ‘||x.sequence_name||’ increment by 100′;
execute immediate (sqlstmt);
sqlstmt := ‘select ‘||x.sequence_name||’.nextval from dual’;
execute immediate (sqlstmt);
sqlstmt := ‘alter sequence ‘||x.sequence_name||’ increment by 1′;
execute immediate (sqlstmt);
end loop;
end;
This doesn’t do anything to the sequences, so I am relegated to running the scripts individually.
It works like that :
declare
sqlstmt varchar2(200);
begin
for x in (select sequence_name from user_sequences) loop
sqlstmt := ‘alter sequence ‘||x.sequence_name||’ increment by 10′;
execute immediate sqlstmt;
sqlstmt := ‘declare retval number(10); begin select ‘||x.sequence_name||’.nextval into retval from dual; commit; end;’;
execute immediate sqlstmt;
sqlstmt := ‘alter sequence ‘||x.sequence_name||’ increment by 1′;
execute immediate sqlstmt;
end loop;
end;
I know this is not very clean code, but it seems that a nextval does not work in a pure select statement in execute immediate function.
good solution.
Thanks!
Thanks!!!