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;