How to Reset a Sequence in Oracle

November 19, 2010

in sequences

reset-oracle-sequence

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

{ 7 comments… read them below or add one }

AMER KHAN INAMDAR March 1, 2011 at 1:59 pm

is it possible that view can be based on the sequence and index

Ben Martinka August 10, 2013 at 2:16 am

Very helpful. I’ve taken your script to the next level and added parameters that allow you to reset the sequence to any specified value zero or greater, or specifying a negative value will reset the sequence to resume with the next higher integer than the maximum value in the specified table, with sequence and primary key names derived from the table name. You can change those initializations to suit your own standard, or could add them as input variables if desired. An optional output variable is currently commented out but could be enabled if you want the resulting value the sequence will assign next. Here’s the code (not sure whether formatting will come through though:

— Author: Ben Martinka 8/9/13
CREATE or REPLACE PROCEDURE
reset_seq(
— p_nxt out number,
p_tbl in varchar2,
p_new in number DEFAULT -1
— p_min in number DEFAULT 0
)

IS
l_new number;
l_nxt number;
l_cnt number;
l_inc number;
l_min number;
l_key varchar2(256);
l_seq varchar2(256);
BEGIN
— Derive primary key & sequence names from table name
— according to database design standards.
l_key := p_tbl || ‘ID';
l_seq := p_tbl || ‘_PK_SEQ1′;

IF p_new >= 0 THEN

l_new := p_new;

ELSE
— p_new < 0
— Can't seem to get a startat value of zero to work with
— this routine without a lot of strange problems with negative
— increments and minimum values so just treating it same
— as a negative number as a flag to reset sequence to next
— value after highest value in p_tbl since few sequences
— use zero.

— See if table has any rows.
execute immediate
'select count(' || l_key || ') from ' || p_tbl INTO l_cnt;

IF l_cnt = 0 THEN
— No rows so assume first row will start at zero.
— User can select .nextval once afterward or explicitly
— set p_new := 1 if a start of one is preferred.
l_new := 0;

ELSE
— Get last used PK ID and increment for new value.
execute immediate
'select max(' || l_key || ') + 1 as nextkey from ' ||
p_tbl INTO l_new;

END IF;

END IF;

— Retrieve the next value of the sequence.
— (Currvalue would be safer in case procedure fails
— but it won't work unless nextval has already been called
— once during the session.)
— Note: LAST_NUMBER property of sequence is not necessarily
— the same as nextval, but rather the next value after
— the currently buffered values, so don't rely on it in testing.
execute immediate
'select ' || l_seq || '.nextval from dual' INTO l_nxt;

— If p_new = next value then sequence was already in
— desired state and this just restores it to that value
— using an increment of -1. Otherwise is creates a
— positive or negative increment equal to the difference
— in order to close the gap upward or downward
— from the current next value to the desired next value.

l_inc := l_new – l_nxt – 1;

IF l_new = 0 THEN
l_min := -1;
ELSE
l_min := 0;
END IF;

execute immediate
'alter sequence ' || l_seq ||
' increment by ' || l_inc || ' minvalue ' || l_min;

— Select once from the sequence, to set it to p_new
— or to one more than the last value in the table
— if p_new <= 0 (flag to take this action).
execute immediate
'select ' || l_seq || '.nextval from dual' INTO l_nxt;

execute immediate
'alter sequence ' || l_seq ||
' increment by 1 minvalue ' || l_min;

— p_nxt := l_new;
RETURN;

END;

Ben Martinka August 10, 2013 at 2:21 am

Sorry, I forgot to implement my final parameter in the above, which also allows you to change the minimum value for the sequence at the same time. p_new is the new starting value. If that is zero, or if a negative value and no rows are found in the table (which will assume a sequence starting at zero), then the min value will be forced to -1 regardless of value of p_min or it will fail.

Here’s the corrected code:

— Author: Ben Martinka 8/9/13
CREATE or REPLACE PROCEDURE
reset_seq(
— p_nxt out number,
p_tbl in varchar2,
p_new in number DEFAULT -1,
p_min in number DEFAULT 0
)

IS
l_new number;
l_nxt number;
l_cnt number;
l_inc number;
l_min number;
l_key varchar2(256);
l_seq varchar2(256);
BEGIN
— Derive primary key & sequence names from table name
— according to database design standards.
l_key := p_tbl || ‘ID';
l_seq := p_tbl || ‘_PK_SEQ1′;

IF p_new >= 0 THEN

l_new := p_new;

ELSE
— p_new < 0
— Can't seem to get a startat value of zero to work with
— this routine without a lot of strange problems with negative
— increments and minimum values so just treating it same
— as a negative number as a flag to reset sequence to next
— value after highest value in p_tbl since few sequences
— use zero.

— See if table has any rows.
execute immediate
'select count(' || l_key || ') from ' || p_tbl INTO l_cnt;

IF l_cnt = 0 THEN
— No rows so assume first row will start at zero.
— User can select .nextval once afterward or explicitly
— set p_new := 1 if a start of one is preferred.
l_new := 0;

ELSE
— Get last used PK ID and increment for new value.
execute immediate
'select max(' || l_key || ') + 1 as nextkey from ' ||
p_tbl INTO l_new;

END IF;

END IF;

— Retrieve the next value of the sequence.
— (Currvalue would be safer in case procedure fails
— but it won't work unless nextval has already been called
— once during the session.)
— Note: LAST_NUMBER property of sequence is not necessarily
— the same as nextval, but rather the next value after
— the currently buffered values, so don't rely on it in testing.
execute immediate
'select ' || l_seq || '.nextval from dual' INTO l_nxt;

— If p_new = next value then sequence was already in
— desired state and this just restores it to that value
— using an increment of -1. Otherwise is creates a
— positive or negative increment equal to the difference
— in order to close the gap upward or downward
— from the current next value to the desired next value.

l_inc := l_new – l_nxt – 1;

IF l_new = 0 THEN
l_min := -1;
ELSE
l_min := p_min;
END IF;

execute immediate
'alter sequence ' || l_seq ||
' increment by ' || l_inc || ' minvalue ' || l_min;

— Select once from the sequence, to set it to p_new
— or to one more than the last value in the table
— if p_new <= 0 (flag to take this action).
execute immediate
'select ' || l_seq || '.nextval from dual' INTO l_nxt;

execute immediate
'alter sequence ' || l_seq ||
' increment by 1 minvalue ' || l_min;

— p_nxt := l_new;
RETURN;

END;

Ben Martinka August 10, 2013 at 2:26 am

Ugh – looks like all my double hyphen comment indicators got converted to em-dashes upon posting. Let’s try again with quadruple dashes …

—- Author: Ben Martinka 8/9/13
CREATE or REPLACE PROCEDURE
reset_seq(
—- p_nxt out number,
p_tbl in varchar2,
p_new in number DEFAULT -1,
p_min in number DEFAULT 0
)

IS
l_new number;
l_nxt number;
l_cnt number;
l_inc number;
l_min number;
l_key varchar2(256);
l_seq varchar2(256);
BEGIN
—- Derive primary key & sequence names from table name
—- according to database design standards.
l_key := p_tbl || ‘ID';
l_seq := p_tbl || ‘_PK_SEQ1′;

IF p_new >= 0 THEN

l_new := p_new;

ELSE
—- p_new < 0
—- Can't seem to get a startat value of zero to work with
—- this routine without a lot of strange problems with negative
—- increments and minimum values so just treating it same
—- as a negative number as a flag to reset sequence to next
—- value after highest value in p_tbl since few sequences
—- use zero.

—- See if table has any rows.
execute immediate
'select count(' || l_key || ') from ' || p_tbl INTO l_cnt;

IF l_cnt = 0 THEN
—- No rows so assume first row will start at zero.
—- User can select .nextval once afterward or explicitly
—- set p_new := 1 if a start of one is preferred.
l_new := 0;

ELSE
—- Get last used PK ID and increment for new value.
execute immediate
'select max(' || l_key || ') + 1 as nextkey from ' ||
p_tbl INTO l_new;

END IF;

END IF;

—- Retrieve the next value of the sequence.
—- (Currvalue would be safer in case procedure fails
—- but it won't work unless nextval has already been called
—- once during the session.)
—- Note: LAST_NUMBER property of sequence is not necessarily
—- the same as nextval, but rather the next value after
—- the currently buffered values, so don't rely on it in testing.
execute immediate
'select ' || l_seq || '.nextval from dual' INTO l_nxt;

—- If p_new = next value then sequence was already in
—- desired state and this just restores it to that value
—- using an increment of -1. Otherwise is creates a
—- positive or negative increment equal to the difference
—- in order to close the gap upward or downward
—- from the current next value to the desired next value.

l_inc := l_new – l_nxt – 1;

IF l_new = 0 THEN
l_min := -1;
ELSE
l_min := p_min;
END IF;

execute immediate
'alter sequence ' || l_seq ||
' increment by ' || l_inc || ' minvalue ' || l_min;

—- Select once from the sequence, to set it to p_new
—- or to one more than the last value in the table
—- if p_new <= 0 (flag to take this action).
execute immediate
'select ' || l_seq || '.nextval from dual' INTO l_nxt;

execute immediate
'alter sequence ' || l_seq ||
' increment by 1 minvalue ' || l_min;

—- p_nxt := l_new;
RETURN;

END;

Ben Martinka August 10, 2013 at 2:36 am

Ignore comment block after first ELSE – forgot to delete that after I got it working.

KK November 24, 2014 at 6:43 pm

This approach works for me, though I’ve had to tweak it for my own benefit. Thanks!
Not sure if it introduces GAP in the sequence. I’ve not witnessed any so far, but curious to know..

oratabler November 24, 2014 at 7:26 pm

@KK: The script in the main post changes the start point of the sequence, and then sets the increment back to 1 – this will not introduce gaps of course.

Leave a Comment

Previous post:

Next post: