This package should be installed as the GGATE user on the 12c Database (Target)
Before installing you need to create the database link and tables from here
Function MOL_SEQUENCE_CHECK_LINK contains a database link that may need to be renamed.
-- 12c Package
create or replace package MOL_SEQUENCES
AUTHID CURRENT_USER
as
FUNCTION mol_sequence_check (seq_owner in varchar2, seq_name in varchar2) RETURN number;
FUNCTION mol_sequence_check_link (seq_owner in varchar2, seq_name in varchar2) RETURN number;
PROCEDURE mol_sequence_sync (seq_owner in varchar2, seq_name in varchar2, sync_value IN number);
PROCEDURE mol_seq_alternate (sequence_owner varchar2);
PROCEDURE mol_inc1 (seq_owner varchar2);
end MOL_SEQUENCES;
/
create or replace package body MOL_SEQUENCES as
-- Check Sequence Value on 12c Database
FUNCTION mol_sequence_check (seq_owner in varchar2, seq_name in varchar2)
RETURN NUMBER
AS
seq_number number(12);
BEGIN
select last_number into seq_number
from all_sequences
where sequence_owner = upper(seq_owner)
and sequence_name = upper(seq_name);
return seq_number;
EXCEPTION
when no_data_found then
return -1;
when others then raise;
end mol_sequence_check;
-- Check Sequence Value on 10g Database
FUNCTION mol_sequence_check_link (seq_owner IN varchar2, seq_name IN varchar2)
RETURN NUMBER
AS
seq_number number(12);
begin
select last_number into seq_number
from all_sequences@ggandracaa
where sequence_owner = upper(seq_owner)
and sequence_name = upper(seq_name);
return seq_number;
EXCEPTION
when no_data_found then
return -1;
when others then raise;
end mol_sequence_check_link;
-- Find difference in values between 10g and 12c. If greater than sync_value then move sequence value closer
PROCEDURE mol_sequence_sync (seq_owner IN varchar2, seq_name IN varchar2, sync_value IN number)
AS
v_seq_diff number(12);
v_local_val number(12);
BEGIN
-- Get difference between both sequence values
select mol_sequence_check_link(seq_owner, seq_name) - mol_sequence_check(seq_owner, seq_name)
into v_seq_diff
from dual;
-- If difference is more than stated amount then resynchronise values between 2 databases
If v_seq_diff > sync_value
then for x in 1..round(v_seq_diff/2,0)
LOOP
execute immediate 'select ' || seq_owner || '.' || seq_name || '.nextval from dual' into v_local_val;
END LOOP;
insert into ggate.sync_sequences_log values (seq_owner, seq_name, v_seq_diff, SYSTIMESTAMP);
commit;
End If;
END mol_sequence_sync;
-- Check if sequence value on 10g is odd or even. Increment sequence by 2 and set up so it is reverse odd/even.
PROCEDURE mol_seq_alternate (sequence_owner IN varchar2)
AS
vSeqValInt number(12);
vSeqValuePoc number(12);
vSeqName varchar2(100);
cursor SeqValueInt is
select sequence_name, ValueINT from ggate.mol_gg_sequences@ggandracaa
where upper(owner) = upper(sequence_owner);
BEGIN
open SeqValueInt;
loop
fetch SeqValueInt into vSeqName, vSeqValInt;
exit when SeqValueInt%NOTFOUND;
dbms_output.put_line(vSeqName);
execute immediate 'alter sequence ' || sequence_owner || '.' || vSeqName || ' increment by 2';
execute immediate 'select ' || sequence_owner || '.' || vSeqName || '.nextval from dual' into vSeqValuePoc;
if mod(vSeqValInt,2) = mod(vSeqValuePoc,2)
then
execute immediate 'alter sequence ' || sequence_owner || '.' || vSeqName || ' increment by 1';
execute immediate 'select ' || sequence_owner || '.' || vSeqName || '.nextval from dual' into vSeqValuePoc;
execute immediate 'alter sequence ' || sequence_owner || '.' || vSeqName || ' increment by 2';
update mol_gg_sequences@ggandracaa set ValuePOC = vSeqValuePoc where sequence_name = vSeqName and owner = sequence_owner;
commit;
else
update mol_gg_sequences@ggandracaa set ValuePOC = vSeqValuePoc where sequence_name = vSeqName and owner = sequence_owner;
commit;
end if;
end loop;
close SeqValueInt;
END mol_seq_alternate;
-- Reset all sequences back to increment 1
PROCEDURE mol_inc1 (seq_owner IN varchar2)
AS
vSeqName varchar2(100);
cursor SeqName is
select sequence_name from all_sequences
where sequence_owner = upper(seq_owner);
BEGIN
open SeqName;
loop
fetch SeqName into vSeqName;
exit when SeqName%NOTFOUND;
execute immediate 'alter sequence ' || seq_owner || '.' || vSeqName || ' increment by 1';
end loop;
close SeqName;
END mol_inc1;
END MOL_SEQUENCES;
/