在Oracle 11g中DBLINK的创建以及测试
关于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--