Oracle Expert

Sometimes after a database refresh, we discover that the sequence number has fallen behind compared to actual max value in the table. This then results in PK constraint error when we try to insert a new row using the sequence.

To realign the sequence, you can obviously drop/recreate/alter if you have the right privilege, however here is a tip of how to do it if you do not have the schema owner privilege.

declare
v_table number;
v_seq number;
v_diff number;
begin
select max(seq_id) into v_table from TABLE1;
select last_number into v_seq from all_sequences where sequence_owner=’OWNER01′ and sequence_name=’TABLE1_SEQ’ ;

select v_seq - v_table into v_diff from dual;

if v_diff < 0
then
select (v_diff * -1) + 2 into v_diff from dual;

for i in 1 .. v_diff
loop
select TABLE1_SEQ.nextval into v_table from dual;
end loop;

end if;

end;
/