oracle中dblink的创建与使用

确定创建dblink要点：
1、确认远程服务器能够ping通，即tnsping要能通。
2、确认远程数据库oracle字符集的一致性。

本地创建dblink访问远程数据库， 在本机创建访问远程数据库脚本。

实例：在bmi用户上创建连接imgbmi用户的dblink
create database link imgbmi
connect to imgbmi
identified by imgbmi
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)';

在imgbmi用户上创建连接bmi用户的dblink
create database link db_list
connect to bmi
identified by bmi
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';

-- Drop existing database link
drop database link https://www.360docs.net/doc/2c474368.html,;
-- Create database link
create database link https://www.360docs.net/doc/2c474368.html,
connect to BMI14
using '10.117.130.29:1521/ora29a';

select * from sex@imgbmi;
select * from sex@https://www.360docs.net/doc/2c474368.html,;

--dba权限用户给用户创建dblink权限
grant create database link to user ; --只有user用户能使用的dblink
grant create public database link to user ;--所有用户都可以使用的dblink
grant drop public database link to user; --删除dblink的权限


grant debug any procedure, debug connect session to imgbmi;