How to Reset a Sequence in Oracle

Oracle does not a have ready-made command to restart a sequence. There is no direct way to do this:

alter sequence test_seq restart with 0;

But I have a simple workaround to share with you. This article gives you a script to restart a sequence, explains how it works and shows you a test run with a sample sequence.

Let’s take any sequence s_autoinc, with current value:

SQL> select s_autoinc.currval from dual;

   CURRVAL
----------
        41

…and you want to take the sequence value back to 0.

To restart a standard sequence in Oracle, I have created a small PL/SQL procedure, courtesy the brilliant Tom Kyte. Here it comes.

Script to reset sequence

create or replace
procedure reset_sequence(p_seq in varchar2)
is
    l_value number;
begin
-- Select the next value of the sequence

    execute immediate
    'select ' || p_seq || 
    '.nextval from dual' INTO l_value;

-- Set a negative increment for the sequence, 
-- with value = the current value of the sequence

    execute immediate
    'alter sequence ' || p_seq || 
    ' increment by -' || l_value || ' minvalue 0';

-- Select once from the sequence, to 
-- take its current value back to 0

    execute immediate
    'select ' || p_seq || 
    '.nextval from dual' INTO l_value;

-- Set the increment back to 1

    execute immediate
    'alter sequence ' || p_seq || 
    ' increment by 1 minvalue 0';
end;
/

How the script works

The script uses dynamic SQL to execute four simple steps.

Step 1 selects the next value of the sequence.

Step 2 sets a negative increment for the sequence, with value = the current value of the sequence.

Step 3 selects once from the sequence. This takes its current value back to 0.

Step 4 sets the increment back to 1.

A Test Run

SQL> -- Sequence value before resetting
SQL> select s_autoinc.currval from dual;

   CURRVAL
----------
        41

SQL> -- Run the procedure to reset sequence
SQL> exec reset_sequence ('s_autoinc');

PL/SQL procedure successfully completed.

SQL> -- Bingo!
SQL> select s_autoinc.currval from dual;

   CURRVAL
----------
         0

Why go through this rigmarole? Why not simply drop and recreate the sequence?

You could drop and recreate the sequence, but the disadvantage is this would invalidate all dependent objects (triggers/stored procedures etc.). Far better to alter and reset the sequence.

Caution!

Reset your sequence when others are not using it simultaneously, else they (or you) may get this error:

ORA-08004: sequence SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated