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
{ 9 comments… read them below or add one }
is it possible that view can be based on the sequence and index
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;
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;
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;
Ignore comment block after first ELSE – forgot to delete that after I got it working.
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..
@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.
Thanks – worked perfectly for us….very useful!
This is my version where the input is table_name, key_column_name e sequence_name.
create or replace PROCEDURE reset_seq (
table_name IN VARCHAR2,
key_column_name IN VARCHAR2,
sequence_name IN VARCHAR2
) IS
new_value NUMBER;
next_value NUMBER;
row_count NUMBER;
increment_value NUMBER;
min_value NUMBER;
BEGIN
— See if table has any rows.
EXECUTE IMMEDIATE ‘select count(‘
|| key_column_name
|| ‘) from ‘
|| table_name
INTO row_count;
IF row_count = 0 THEN
— No rows so assume first row will start at 1.
new_value := 1;
ELSE
— Get last used PK ID and increment for new value.
EXECUTE IMMEDIATE ‘select max(‘
|| key_column_name
|| ‘) + 1 as nextkey from ‘
|| table_name
INTO new_value;
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 ‘
|| sequence_name
|| ‘.nextval from dual’
INTO next_value;
— 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.
increment_value := new_value – next_value – 1;
min_value := 0;
EXECUTE IMMEDIATE ‘alter sequence ‘
|| sequence_name
|| ‘ increment by ‘
|| increment_value
|| ‘ minvalue ‘
|| min_value;
— Select once from the sequence, to set it to
— one more than the last value in the table
EXECUTE IMMEDIATE ‘select ‘
|| sequence_name
|| ‘.nextval from dual’
INTO next_value;
EXECUTE IMMEDIATE ‘alter sequence ‘
|| sequence_name
|| ‘ increment by 1 minvalue ‘
|| min_value;
RETURN;
END;