Oracle中dblink的使用

dblink的作用

当用户要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中必须创建了远程数据库的dblink，通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。

创建案例

create public database link dblink_name connect to remote_username identified by remote_password using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

 

-- 创建dblink
create public database link db105
connect to system identified by hzsoft  
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.103.105)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

-- 查看dblink
select * from all_db_links;

-- 删除
drop public database link db105;

-- 使用
select service_name from pre_service@DB105