Below is a section of SQL that when run will reset sequences to match the max+1 in the associated table :-

create table tmp_maxid (maxid number(20),rowcount number(20));

create or replace procedure sp_resetseq as

vnextid number; vexists number; vtable varchar2(100); voldtable varchar2(100); vsertable varchar2(105); vcount number;

CURSOR c_seq IS select distinct substr(sequence_name,0,length(sequence_name)-2) name from user_sequences;

BEGIN

select count(*) into vexists from user_sys_privs where PRIVILEGE like ‘%CREATE%SEQUENCE%’ ;

if vexists > 0 then

FOR rec1 IN c_seq LOOP

vtable := rec1.name ; voldtable := rec1.name ;

select count(*) into vexists from user_tab_columns where table_name=vtable and column_name=’ID’;

if vexists < 1 then

vsertable := ‘%’||vtable||’ID%’ ;

select count(ut.table_name) into vcount from user_source us, user_triggers ut where us.type=’TRIGGER’ and upper(us.text) like vsertable and us.name=ut.trigger_name;

if vcount = 1 then

select ut.table_name into vtable from user_source us, user_triggers ut where us.type=’TRIGGER’ and upper(us.text) like vsertable and us.name=ut.trigger_name;

dbms_output.put_line(‘WARNING : Using table ‘||vtable||’ for sequence ‘||rec1.name||’ID’);

select count(*) into vexists from user_tab_columns where table_name=vtable and column_name=’ID’;

elsif vcount > 1 then

dbms_output.put_line(‘Problem sequence ‘||vtable||’ID is used by ‘||vcount||’ tables’);

select min(ut.table_name) into vtable from user_source us, user_triggers ut where us.type=’TRIGGER’ and upper(us.text) like vsertable and us.name=ut.trigger_name;

dbms_output.put_line(‘WARNING : Using MIN table ‘||vtable||’ for sequence ‘||rec1.name||’ID’);

select count(*) into vexists from user_tab_columns where table_name=vtable and column_name=’ID’;

end if;

end if;

– dbms_output.put_line(‘Table ‘||vtable||’ exists : ‘||vexists);

if vexists > 0 then

execute immediate ‘delete from tmp_maxid’;

execute immediate ‘insert into tmp_maxid select 0,count(*) from ‘||vtable;

select rowcount into vcount from tmp_maxid;

if vcount > 0 then

execute immediate ‘update tmp_maxid set maxid=(select max(id)+1 from ‘||vtable||’)';

select maxid into vnextid from tmp_maxid;

else

vnextid := 1;

end if;

dbms_output.put_line(‘Sequence ‘||voldtable||’ID set to ‘||vnextid);

execute immediate ‘drop sequence ‘||voldtable||’ID’ ;

execute immediate ‘create sequence ‘||voldtable||’ID start with ‘||vnextid ;

execute immediate ‘grant select on ‘||voldtable||’ID to all_access’ ;

else

dbms_output.put_line(‘WARNING : Table ‘||vtable||’ does not exist, find sequence : ‘||voldtable||’ID’);

end if;

END LOOP ;

else

dbms_output.put_line(‘ERROR : Please grant create sequence to the user.’);

end if;

end sp_resetseq; /

show errors;

set serveroutput on size 999999;

exec sp_resetseq;

drop procedure sp_resetseq;

drop table tmp_maxid;