Verse Of The Day

Friday, February 03, 2006

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.

max_id number;
tmp number;
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;

No comments: