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.

oracle, sql, dba, database administration, database development