How do I reset a sequence in Oracle?

sql

sequence-reset

oracle

data-integrity

byAnton Shumikhin·Nov 7, 2024

Resetting a sequence in Oracle can be achieved using ALTER SEQUENCE and a negative increment trick:

ALTER SEQUENCE my_seq INCREMENT BY -currval MINVALUE 0; -- Be careful not to step on any data toes here.
SELECT my_seq.NEXTVAL FROM dual; -- Shrink to MINVALUE.
ALTER SEQUENCE my_seq INCREMENT BY 1 START WITH 100; -- We are all fresh and ready to start anew!

Replace currval with the current maximum value of the sequence to be subtracted. This sets the next my_seq.NEXTVAL to 100.

Handy alternatives

The standard approach is perfect for one-off resets, but what about when we need an automated, dynamic solution? We resort to a fabulous trick by the remarkable Tom Kyte:

DECLARE
  val NUMBER;
BEGIN
  SELECT max(column_name) INTO val FROM table_name; -- The table and column belong to you, choose wisely!
  EXECUTE IMMEDIATE 'ALTER SEQUENCE my_seq INCREMENT BY -' || val || ' MINVALUE 0'; -- It's going down, I'm yelling timber!
  EXECUTE IMMEDIATE 'SELECT my_seq.NEXTVAL FROM dual'; -- Whew, made it to the bottom.
  EXECUTE IMMEDIATE 'ALTER SEQUENCE my_seq INCREMENT BY 1 START WITH ' || val+1; -- Now we get to climb up again. Yay?
END;

And for the lucky ones running Oracle 18c and above, your life just got simpler:

ALTER SEQUENCE my_seq RESTART START WITH 1; -- Because why walk when you can teleport?

Data integrity? Anyone?

Resetting sequences might seem fun, till it ruins your data. Pay heed to the repercussions of dropping/creating sequences on existing data.

Masters guide to sequence reset

Bulk reset

If all your sequences flew out of sync, here's how to get them back in line:

CREATE OR REPLACE PROCEDURE Reset_All_Sequences IS -- This little genie does the magic.
BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE seq1 INCREMENT BY -currval1 MINVALUE 0'; -- Setback time!
  EXECUTE IMMEDIATE 'SELECT seq1.NEXTVAL FROM dual'; -- Journey to the start.
  EXECUTE IMMEDIATE 'ALTER SEQUENCE seq1 INCREMENT BY 1'; -- All set for the race!
  -- Repeat these steps for each sequence.
END;

Tricky problems

Quick reminder, resetting sequences can stir pandemonium in your applications. Be wary of sequence caches and race conditions when executing concurrent operations.