Setting sequences to only even or odd numbers

When using a hot/hot environment with GoldenGate, it is necessary to avoid PK collisions. The standard approach is for one side to use even numbers and the other to use odd.

Here’s a script that will change all the sequences within a schema to either even or odd:

DECLARE
PROCEDURE set_sequence (i_owner IN VARCHAR2,
i_sequence IN VARCHAR2,
i_mod IN NUMBER) IS
v_sql_block VARCHAR2(4000);
v_seq_val NUMBER;
v_counter NUMBER :=0;


BEGIN

   IF i_mod NOT IN (0,2) THEN RAISE_APPLICATION_ERROR(-20001,'Must use 0 or 1'); END IF;
   
   EXECUTE IMMEDIATE 'alter sequence '||i_owner||'.'||i_sequence||' increment by 2';
   EXECUTE IMMEDIATE 'SELECT '||i_owner||'.'||i_sequence||'.NEXTVAL FROM DUAL' INTO v_seq_val;

   WHILE MOD(v_seq_val,2) != i_mod
         AND v_counter <= 1000 LOOP
      EXECUTE IMMEDIATE 'alter sequence '||i_owner||'.'||i_sequence||' increment by 1';
      EXECUTE IMMEDIATE 'SELECT '||i_owner||'.'||i_sequence||'.NEXTVAL FROM DUAL' INTO v_seq_val;
      EXECUTE IMMEDIATE 'alter sequence '||i_owner||'.'||i_sequence||' increment by 2';
      EXECUTE IMMEDIATE 'SELECT '||i_owner||'.'||i_sequence||'.NEXTVAL FROM DUAL' INTO v_seq_val;
      v_counter := v_counter + 1;
   END LOOP;

   IF MOD(v_seq_val,2) != i_mod
   THEN
      IF i_mod = 0
         THEN
         dbms_output.put_line(i_sequence||' is not odd!');
      ELSE
         dbms_output.put_line(i_sequence||' is not even!');
      END IF;

   END IF;
END; --set_sequence

BEGIN
   FOR rSeq IN (SELECT sequence_owner,
                       sequence_name
                  FROM dba_sequences
                 WHERE sequence_owner = 'YOUR OWNER HERE') LOOP
      --Pass 0 for even sequences, 1 to see sequences to odd.
      set_sequence(rSeq.sequence_owner,
                   rSeq.sequence_name,
                   0);
   END LOOP;
END;
/