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; /