Re-Synching Oracle Sequence With PL/SQL

Sometimes an Oracle Sequence can get out of sync. For instance, if a sequence gets dropped or wraps around to zero while there is data in the table, or if data is loaded while the sequence is disabled. Here is a quick PL/SQL block to reset the sequence to 1 more than the max id in the data.

Replace column_name, table_name, sequence_name below and then run it.

declare
  max_id number;
  tmp number;
begin
  max_id := 0;
  select max(column_name) into max_id from table_name;
  if max_id > 0 then
    for i in 1..max_id loop
      select sequence_name.nextval into tmp from dual;
      exit when tmp > max_id;
    end loop;
  end if;
end;
/