Reset sequence values to align with table

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