用PLSQL创建oracle库到另一个可访问oracle库的dblink(Database_links)以便查询对比数据

1.在本地VPN下创建医院HIS系统的dblink

 

 

生成之后可以点击查看sql

创建dblink的sql:

-- Create database link
create database link NPEY
connect to XINDIAN
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =npey)
)
)';

 

实际应用：

在CDRDB库中创建了HIS库的dblink：@xindian，可以排查出在CDRDB库中同步的数据比HIS提供的视图数据多的数据和少数据

1、查询CDRDB缺失的数据，比HIS少的数据

 select * from  zhiydba.TB_HIS_DRUG_DISPENSE_REC@xindian a where REPORTDATE>=sysdate-7 and REPORTDATE<=sysdate and a.sourceid not in (select sourceid from TB_HIS_DRUG_DISPENSE_REC where REPORTDATE>=sysdate-7 and REPORTDATE<=sysdate)

2、查询CDRDB多余的数据，比HIS多的数据

 select * from  TB_HIS_DRUG_DISPENSE_REC a where REPORTDATE>=sysdate-7 and REPORTDATE<=sysdate and a.sourceid not in (select sourceid from zhiydba.TB_HIS_DRUG_DISPENSE_REC@xindian where REPORTDATE>=sysdate-7 and REPORTDATE<=sysdate)