Tag: update

Below is procedure that updates all table sequences to match the max ID currently in a table. Useful when there is a mismatch of values.

-- Update all sequences to match actual table values
DECLARE
v_max VARCHAR2 (1000);
BEGIN

FOR rec IN (
select tabs.table_name table_name,
seqs.sequence_name seq_name,
seqs.last_number
from user_tables tabs
join user_triggers trigs
on trigs.table_name = tabs.table_name
join user_dependencies deps
on deps.name = trigs.trigger_name
join user_sequences seqs
on seqs.sequence_name = deps.referenced_name
where last_number = 1 --only sequnces that have last number 1
)
LOOP
--Info print
--dbms_output.put_line(rec.table_name);
--dbms_output.put_line(rec.seq_name);
--dbms_output.put_line(rec.last_number);

--reset sequence last number to 0
EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY -' || rec.last_number ;
EXECUTE IMMEDIATE 'SELECT ' || rec.seq_name || '.NEXTVAL FROM DUAL' ;

--Find highest id in table
EXECUTE IMMEDIATE 'select max(id)+1 from ' || rec.table_name INTO v_max;

--dbms_output.put_line(v_max);

EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY ' || v_max ;

EXECUTE IMMEDIATE 'SELECT ' || rec.seq_name || '.NEXTVAL FROM DUAL' ;

EXECUTE IMMEDIATE 'ALTER sequence ' || rec.seq_name || ' INCREMENT BY 1' ;

END LOOP;
END;
/