Reset Sequence value in Oracle

posted Jan 17, 2012, 9:54 AM by Sachchida Ojha
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;
Comments