Back
In PostgreSQL, I can do something like this:
ALTER SEQUENCE serial RESTART WITH 0;
Is there an Oracle equivalent?
Following is the good method for resetting any sequence to 0 from Oracle:
[email protected]> create or replaceprocedure 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;/
[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;
'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
' minvalue 0';
'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/
For more information, you can refer to these links: Dynamic SQL to reset sequence value & How to reset sequences?
31k questions
32.8k answers
501 comments
693 users