Changing the Current Value of Oracle Sequences
Categories: Database Administration, Information Technology, OracleThanks 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.
10 Responses to “Changing the Current Value of Oracle Sequences”
-
ko Says:
November 14th, 2005 at 3:11 amYou can specify a negative increment to decrease the sequence.
alter sequence id_sequence increment by -142900;
-
Jon Says:
November 14th, 2005 at 9:21 amThanks ko! This is a great tip!
-
Amy Says:
April 24th, 2006 at 11:09 amThis was very helpful! Your site is great.
-
Nikhil Says:
January 17th, 2007 at 5:36 amThanks, it was correctly mentioned.
-
anydoby Says:
November 7th, 2007 at 5:14 amthanks a lot
-
Kevin Says:
November 26th, 2007 at 1:21 pmnice 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?
-
Jon Emmons Says:
November 28th, 2007 at 10:07 pmNot 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.
-
Frank Says:
February 19th, 2008 at 3:31 pmI 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.
-
Serge Says:
March 19th, 2008 at 1:05 pmIt 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.
-
kcgupta Says:
November 12th, 2009 at 5:54 amgood solution.
Thanks!
