oracle dblink 测试,在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--