Adjust Oracle sequence to use odd and even number respectively on a Production and DR database

Our production databases are replicated to DR databases through SharePlex replcation. Recently, We have a need to enable two-way replication. i.e. in addition to replication from production to DR, we also need DR to prodcution. I was thus assigned a task to adjust the sequences in production to use odd number and sequences in DR to use even number.

The sequneces in production and DR are out-of-sync currently, as we don't replicate sequence from production to DR. That means if on production a sequence's last number is 1000, whereas on DR, the number could be 1.

Testing for a while, I found that when generating the DDL for a sequence through DBMS_METADATA package, the number following the 'start with' clause is the last number of DBA_SEQUENCES view.

For example:

SQL> select sequence_name, last_number from dba_sequences
where sequence_owner=user;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
TEST2_SEQ                              107
TEST3_SEQ                              107
TEST_SEQ                               175


SQL> ;
  1* SELECT DBMS_METADATA.GET_DDL(upper('&OBJTYPE'), upper('&OBJNAME') , upper('&OWNER'))  ddl_string from dual
SQL> /
Enter value for objtype: sequence
Enter value for objname: TEST_SEQ
Enter value for owner: abc

   CREATE SEQUENCE  "ABC"."TEST_SEQ"  MINVALUE 1 MAXVALUE 1.00000000000000E+27
 INCREMENT BY 98 START WITH 175 CACHE 20 NO
ORDER  NOCYCLE ;

I thus developed the following plan:

1. On Prod, run script seq_odd.sql to change seq number to odd and increment by to 2
2. On prod, run script seq_ddl_gen.sql to generate sequence ddl script: seq_ddl.sql
3. SCP seq_ddl.sql to DR server
4. On DR, run script seq_drop_gen.sql and then seq_drop.sql to drop sequence
5. On DR, run script seq_ddl.sql generated in step 2 to re-create all sequences
6. On DR, run script seq_incr1_gen.sql, seq_incr1.sql to modify sequence increment by to 1;
7. On DR, run script seq_nextval_gen.sql, seq_nextval.sql to make the currval of sequences to be even number,
8. on DR, run script seq_incr2_gen.sql, seq_incr2.sql to modify sequence increment by to 2;

The above scripts are simple execept for seq_odd.sql, I wrote PL/SQL code for this:

---- seq_odd.sql -----
declare
  seqown varchar2(30);
  seqname varchar2(30);
  sqlstmt varchar2(1000);
  cval  number;
  incr  number;
  cursor  seq_cur is
      select sequence_owner, sequence_name
        from dba_sequences
        where sequence_owner is [some_condition];
begin
  open seq_cur;
  loop
    fetch seq_cur into seqown, seqname;
    exit when seq_cur%notfound;
    sqlstmt := 'select '  seqown  '.'  seqname  '.nextval  from dual';
    execute immediate sqlstmt into cval;

    if ( mod(cval, 2) = 0 )
    then
-- ensure the current val is odd number
-- first change increment by 1
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 1';
       execute immediate sqlstmt;

       sqlstmt := 'select '  seqown  '.'  seqname  '.nextval  from dual';
       execute immediate sqlstmt into cval;
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 2';
       execute immediate sqlstmt;
    else
-- already an odd number
       sqlstmt := 'alter sequence '  seqown  '.'  seqname  ' increment by 2';
       execute immediate sqlstmt;
    end if;
  end loop;
  close seq_cur;

-- follwing code is to verify ----
---- end of seq_odd.sql --------