From 12.1 onwards, you can do this: alter sequence <sequence_name> restart start with 1; but before that, there is no "alter sequence reset" but you can, by playing with the increment by, reset it.
Use the ALTER SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.
The syntax to create a sequence in Oracle is: CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; sequence_name. The name of the sequence that you wish to create.
The DROP SEQUENCE statement allows you to remove a sequence from the database. In this syntax, specify the name of the sequence that you want to remove after the DROP SEQUENCE keywords. If you don't specify the schema to which the sequence belongs, Oracle will remove the sequence in your own schema.
Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too.
[email protected]>
create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
l_val number;
begin
execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_val;
execute immediate
'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
' minvalue 0';
execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_val;
execute immediate
'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/
From this page: Dynamic SQL to reset sequence value
Another good discussion is also here: How to reset sequences?
A true restart is not possible AFAIK. (Please correct me if I'm wrong!).
However, if you want to set it to 0, you can just delete and recreate it.
If you want to set it to a specific value, you can set the INCREMENT to a negative value and get the next value.
That is, if your sequence is at 500, you can set it to 100 via
ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM dual;
ALTER SEQUENCE serial INCREMENT BY 1;
alter sequence serial restart start with 1;
This feature was officially added in 18c but is unofficially available since 12.1.
It is arguably safe to use this undocumented feature in 12.1. Even though the syntax is not included in the official documentation, it is generated by the Oracle package DBMS_METADATA_DIFF. I've used it several times on production systems. However, I created an Oracle Service request and they verified that it's not a documentation bug, the feature is truly unsupported.
In 18c, the feature does not appear in the SQL Language Syntax, but is included in the Database Administrator's Guide.
This is my approach:
Example:
--Drop sequence
DROP SEQUENCE MY_SEQ;
-- Create sequence
create sequence MY_SEQ
minvalue 1
maxvalue 999999999999999999999
start with 1
increment by 1
cache 20;
My approach is a teensy extension to Dougman's example.
Extensions are...
Pass in the seed value as a parameter. Why? I like to call the thing resetting the sequence back to the max ID used in some table. I end up calling this proc from another script which executes multiple calls for a whole bunch of sequences, resetting nextval back down to some level which is high enough to not cause primary key violations where I'm using the sequence's value for a unique identifier.
It also honors the previous minvalue. It may in fact push the next value ever higher if the desired p_val or existing minvalue are higher than the current or calculated next value.
Best of all, it can be called to reset to a specified value, and just wait until you see the wrapper "fix all my sequences" procedure at the end.
create or replace
procedure Reset_Sequence( p_seq_name in varchar2, p_val in number default 0)
is
l_current number := 0;
l_difference number := 0;
l_minvalue user_sequences.min_value%type := 0;
begin
select min_value
into l_minvalue
from user_sequences
where sequence_name = p_seq_name;
execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_current;
if p_Val < l_minvalue then
l_difference := l_minvalue - l_current;
else
l_difference := p_Val - l_current;
end if;
if l_difference = 0 then
return;
end if;
execute immediate
'alter sequence ' || p_seq_name || ' increment by ' || l_difference ||
' minvalue ' || l_minvalue;
execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_difference;
execute immediate
'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue;
end Reset_Sequence;
That procedure is useful all by itself, but now let's add another one which calls it and specifies everything programmatically with a sequence naming convention and looking for the maximum value used in an existing table/field...
create or replace
procedure Reset_Sequence_to_Data(
p_TableName varchar2,
p_FieldName varchar2
)
is
l_MaxUsed NUMBER;
BEGIN
execute immediate
'select coalesce(max(' || p_FieldName || '),0) from '|| p_TableName into l_MaxUsed;
Reset_Sequence( p_TableName || '_' || p_Fieldname || '_SEQ', l_MaxUsed );
END Reset_Sequence_to_Data;
Now we're cooking with gas!
The procedure above will check for a field's max value in a table, builds a sequence name from the table/field pair and invokes "Reset_Sequence" with that sensed max value.
The final piece in this puzzle and the icing on the cake comes next...
create or replace
procedure Reset_All_Sequences
is
BEGIN
Reset_Sequence_to_Data( 'ACTIVITYLOG', 'LOGID' );
Reset_Sequence_to_Data( 'JOBSTATE', 'JOBID' );
Reset_Sequence_to_Data( 'BATCH', 'BATCHID' );
END Reset_All_Sequences;
In my actual database there are around one hundred other sequences being reset through this mechanism, so there are 97 more calls to Reset_Sequence_to_Data in that procedure above.
Love it? Hate it? Indifferent?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With