Create and test DBLINK in Oracle 11g

The concept of DBLINK is not described in this article. This article mainly introduces the creation of DBLINK.
 
1. Create an environment description
This article will create and test DBLINK on two virtual machines with a red hat 5.5 operating system and 11 GB of Oracle.
 
2. Host Configuration
 
1) configure the host tnsname. ora File
[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)
)
)
 
The information of the database to be connected is written into the tnsname. ora file of the host.
 
3. Create a DBLINK on the host
 
1) In this document, the hr user is used as an example to grant the hr User Permissions:
SYS @ ENMOEDU> grant create public database link, create database link to hr;
 
Grant succeeded.
 
2) create a 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. Test
 
1) delete a row of data in the EMPLOYEES table of the hr user on the connected database
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) view the EMPLOYEES table of the hr user connected to the database on the host
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
 
So far, DBLINK is successfully created.
 
Frank
2014.04.08
 
-- To be continued --