DB Wiki

Below is the code required in order to create …

• Database Links
• Tables
• Scheduled Jobs

… for sequences in a bi-directional format.

-- Create on 10g Database
Create public database link ggmolbb
connect to ggate identified by ggate123
using 'molintpc';

-- Create on 12c Database
Create public database link ggandraca
connect to ggate identified by ggate123
using 'molinta';

Create table sync_sequences_log (owner varchar2(20), sequence_name varchar(50), 
                                 seq_diff number(10), sync_date timestamp);
								 
-- Create on 10g Database
Create table mol_gg_sequences (owner varchar2(20), sequence_name varchar(100),
                               valuint number(12), valuepoc number(12));

-- Example Scheduled Job that runs every hour to keep sequence values in sync.							 
BEGIN
DBMS_SCHEDULER.create_job (
    job_name        => 'sync_sequences_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN mol_sequences.mol_sequence_sync(''MAILONLINE'',''SEQ_ARTICLES_AUTHORS_ID'',100); 
                              mol_sequences.mol_sequence_sync(''MAILONLINE'',''SEQ_ARTICLES_IMAGES_ID'',100);
                              mol_sequences.mol_sequence_sync(''MAILONLINE'',''SEQ_ARTICLE_VERSION'',100);
                              mol_sequences.mol_sequence_sync(''MAILONLINE'',''SEQ_IMAGE_ID'',100);
                              mol_sequences.mol_sequence_sync(''RDRCOMMENTS'',''SEQ_RC_COMMENT_ID'',100); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    enabled         => TRUE);
END;
/
							   
-- Executing Procedures
-- Run on 10g Database to Increment sequences by 2 and record current sequence value
exec mol_sequences.mol_seqoddeven('MAILONLINE'); 

-- Run on 12c Database to Increment sequences by 2 and make sure that sequence value is different to 10g
exec mol_sequences.mol_seq_alternate('MAILONLINE') 

-- Run on either database to make sure that the sequences in both databases are in sync
exec mol_sequences.mol_sequence_sync('MAILONLINE','HIBERNATE_SEQUENCE',100);

-- This resets sequences back to having an increment of 1
exec mol_sequences.mol_inc1('MAILONLINE');