I can't remember where I adopted this code, but I've had it for a while now and I've improved it a little.
create or replace procedure reset_seq (p_seq_name IN VARCHAR2 ,p_new_value IN NUMBER DEFAULT NULL ) IS l_val number; begin execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO l_val; l_val := -l_val+COALESCE(p_new_value,0); --debug( 'alter sequence ' || p_seq_name || ' increment by ' || l_val ||' minvalue 0'); 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; /
So you can reset a sequence manually using
exec reset_seq('my_seq', 117)
Take it a little further and you can make it dependent on the current value in your table.
declare l_id number; begin select max(my_id)+1 -- replace with relevant pk column into l_id from my_table; -- just replace with relevant table reset_seq('my_seq', l_id); -- replace relevant sequence name end; /To take this even further to treat an entire schema, you could do something like this.
declare v_id number; begin FOR r_rec IN ( select table_name -- mapping sequence to table caught me out at first, but luckily we had a good standard in our table comment descriptions. ,(select substr(comments,14,4) from all_tab_comments c where c.table_name = t.table_name and t.owner=c.owner) seq from all_tables t where owner = 'SAGE' order by table_name ) LOOP execute immediate 'select max(id)+1 from '||r_rec.table_name into v_id; dbms_output.put_line(r_rec.table_name||' id:'||v_id||'; seq:'||r_rec.seq||'_id_seq'); reset_seq(r_rec.seq||'_id_seq', v_id); end loop; end; /
Hopefully it might be useful for you one day.
update - I saw this was blogged about recently, but this post was already scheduled - so information in numbers!
Resetting Sequences dlvr.it/32j88h
— Prasanna Peshkar (@PrasannaPeshkar) March 6, 2013