oracle系列之DBLink的使用

赋予权限

-- 查看feng用户是否具备创建database link 权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='FENG';
-- 如果查出结果行说明没有权限，给FENG用户授予创建dblink的权限
grant create public database link to feng;

使用DBLink

- sql创建DBlink，ORCL21是配置在tnsnames.ora的远程数据库的别名
create public database link TESTLINK2 connect to FENG identified by "123456" USING 'ORCL21'

ORCL21=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = reportdb)
)
)

--通过DBLink查询远程数据库的表
select * from testTable@TESTLINK2 order by id;
insert into testTable@TESTLINK2 values(...);
udpate testTable@TESTLINK2 set infoColumn='test' where ...;
delete from testTable@TESTLINK2 where ...;

创建同义词

-- 创建同义词
create synonym TESTSYNONYM FOR testTable@TESTLINK2;
-- 查询ORCL2中FENG用户的表TESTable
SELECT * FROM TESTSYNONYM order by id