关于dblink的概念在本文中不再赘述，，本文主要介绍dblink的创建。
1.创建环境描述
本文将在两台操作系统为红帽5.5版本、装有oracle 11g的虚拟机中进行dblink的创建以及测试工作。
2.主机配置
1）配置主机tnsname.ora文件
[oracle@enmoedu schema]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@enmoedu admin]$ vi tnsnames.ora
# tnsnames.ora network configuration file: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# generated by oracle configuration tools.
enmoedu =
(description =
(address_list =
(address = (protocol = tcp)(host = 192.168.80.10)(port = 1521))
)
(connect_data =
(service_name = enmoedu)
)
)
frank =
(description =
(address_list =
(address = (protocol = tcp)(host = 192.168.80.27)(port = 1521))
)
(connect_data =
(service_name = frank)
)
)
将要连接的数据库的信息写入主机的tnsname.ora文件中。
3.在主机上创建dblink
1）本文中以hr用户为例，首先授予hr用户权限：
sys@enmoedu > grant create public database link,create database link to hr;
grant succeeded.
2）创建dblink
sys@enmoedu > conn hr/oracle
connected.
hr@enmoedu > create database link hrlink connect to hr identified by oracle using 'frank';
database link created.
4.测试
1)在被连接的数据库上的hr用户下的employees表中，删除一行数据
sql> select employee_id,first_name from employees
2 where first_name='pat';
employee_id first_name
----------- --------------------
202 pat
sql> delete from employees
2 where first_name='pat';
1 row deleted.
sql> commit;
commit complete.
2)在主机上查看被连接数据库的hr用户下employees表
hr@enmoedu > select employee_id,first_name from employees@hrlink
2 where first_name='pat';
no rows selected
hr@enmoedu > select employee_id,first_name from employees@hrlink
2 where first_name='shelley';
employee_id first_name
----------- --------------------
205 shelley
至此，dblink创建成功。
frank
2014.04.08
--to be continued--