A combination of UltraCommits statements and a database link, in addition to a stored procedure that you can schedule to automatically run, would serve you well. and its a easiest way to keep this in sync.
--drop create db_link
DROP DATABASE LINK SOURCE_DB;
CREATE DATABASE LINK "SOURCE_DB"
CONNECT TO USER IDENTIFIED BY password USING 'SOURCE_DB';
--drop create sequences
DROP sequence target_seq;
CREATE sequence target_seq start with 6;
--the next two lines run in source db
DROP sequence source_seq;
CREATE sequence source_seq start with 6000;
--take a look at the sequences to get an idea of what to expect
SELECT source_schema.source_seq.nextval@SOURCE_DB source_seq,
target_seq.nextval target_seq
FROM dual;
--create procedure to reset target sequence that you can schedule to automatically run
CREATE OR REPLACE
PROCEDURE reset_sequence
AS
l_source_sequence pls_integer;
l_target_sequence pls_integer;
l_sql VARCHAR2(100);
BEGIN
SELECT source_schema.source_seq.nextval@SOURCE_DB,
target_seq.nextval
INTO l_source_sequence,
l_target_sequence
FROM dual;
l_sql := 'alter sequence target_seq increment by '||to_number(l_source_sequence-l_target_sequence);
EXECUTE immediate l_sql;
SELECT target_seq.nextval INTO l_target_sequence FROM dual;
l_sql := 'alter sequence target_seq increment by 1';
EXECUTE immediate l_sql;
COMMIT;
END reset_sequence;
/
--execute procedure to test it out
EXECUTE reset_sequence;
--review results; should be the same
SELECT source_schema.source_seq.nextval@SOURCE_DB, target_seq.nextval FROM dual;
No comments:
Post a Comment