表级数据同步：通过merge,database links,procedure,job实现

两个库之前表级数据复制，通过在目标端采用merge和database links访问源端并合并操作。将整个操作封装到procdure中后，做了定时任务。

1.准备工作

源端：目标端
SQL> create user dbrep identified by dbrep;
SQL> grant connect,resource to dbrep;
SQL> grant create database link to dbrep;

源端：目标端
conn dbrep/dbrep
create table replica_test1(
id number(10) primary key,
name varchar2(50),
job varchar2(50))
;
目标库
建立好网络连接，并创建dblink
conn dbrep/dbrep
SQL> create database link source_rep
    connect to dbrep identified by dbrep
    using 'DWH01';
2.创建过程

目标端

create or replace procedure rep_merge
as
begin
delete from rep_test2_job
where id not in
(select id from replica_test1@source_rep);
merge into rep_test2_job target
using replica_test1@source_rep rep  
on(target.id=rep.id)
when matched then
update set target.name=rep.name,target.job=rep.job
when not matched then
insert (target.id,target.name,target.job)
values(rep.id,rep.name,rep.job);
commit;
end;

 

3.做定时任务

做个1分钟执行的JOB:
variable job1 number;
begin
 dbms_job.submit(:job1,'rep_merge;',trunc(sysdate+1/1440,'MI'), 'trunc(SYSDATE+1/1440,''MI'')') ;
 dbms_job.run(:job1);
  end;
/

4.测试：

SQL> insert into replica_test1 values(5,'qrtan','sysadmin');
SQL> commit;

SQL> select * from rep_test2_job;
         1 tan                                                dba
         2 bamuta                                             oracle
         3 qr                                                 OS
         4 tanqingru                                          sysdba
         5 qrtan                                              sysadmin

 测试删除，同样能够执行。