Migrating data between environments sometimes requires the need to update the sequence next value.

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.

Scott

update 2 - Also explore identity columns in 12c.

update 1 - 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