Reset your sequence back to Zero "0"

Oracle does not have a command to reset your sequence back to its beginning value zero. Here I am giving a procedure to reset it back.

{code}

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;

/

{/code}

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.