DBLINK同步远程数据

发表于 | 分类于 | 本文总阅读量 次 
create database link PMS805LINK connect to PMS805 identified by PMS805 using '
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.43.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
)';

select * from all_db_links;
select * from LINKTEST01@PMS805LINK;
select * from LINKTEST01@PMS805LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM;
drop database link  PMS805LINK;

创建表(视图)结构

create table(view) LINKTEST02 as select * from LINKTEST01@PMS805LINK where 1=2;

准备sync.sql文件

方式一：

//适用表字段少的情况
delete from LINKTEST02 b where b.ID not in (select c.ID from LINKTEST02 c inner join LINKTEST01@PMS805LINK d on c.ID=d.ID);

merge into LINKTEST02 b using LINKTEST01@PMS805LINK k on (b.ID=k.ID) 
when matched then update set b.NAME=k.NAME,b.AGE=k.AGE 
when not matched then insert values(k.ID,k.NAME,k.AGE);

commit;
quit;

方式二：

//适用表多、字段多的情况
delete from LINKTEST02;
insert into LINKTEST02 select * from LINKTEST01@PMS805LINK;
commit;

创建bat执行脚本

sqlplus PMS805/PMS805@127.0.0.1:1521/ORCL @"E:\sync.sql"

创建定时任务

0%