一.跨库查询
1.创建数据库连接
CREATE public DATABASE link dblink connect TO test IDENTIFIED BY 123456 USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.94.58)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';
CREATE public DATABASE link
连接别名
connect TO用户名
IDENTIFIED BY密码
USING ‘(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =IP
)(PORT =端口
))
)
(CONNECT_DATA =
(SERVICE_NAME =服务名
)
)
)’;
2.调用
SELECT * FROM GGS_DIR.STUDENT @dblink
二.取差集
select * from GGS.STUDENT minus select * from GGS_DIR.STUDENT @dblink;
select * from
原型表
minus select * from对比表
@dblink;
会返回原型表中有但对比表中没有的数据,或对比表与原型表数据不一致的数据
-
表1数据
-
表2数据
-
以表1为原型查询
-
再以表2为原型查询
-
两边结果之和即为两张表不一致的数据,id = 2 的数据就要看你只要以哪张表为准了
注意:
1.oracle取差集不支持以下类型:BLOB、CLOB、BFILE、VARRAY、LONG