基本环境：
机器1： 192.168.56.102 用作dblink的使用者（create database link 语句在此执行）
机器2： 192.168.56.103 用作dblink的源
步骤1：机器2上，启动数据库(oracle_sid:orcl)，启动listener。
步骤2：机器1上，通过运行netca 或者手动修改tnsnames.ora文件，建立到机器1的本地服务名：
生成的tnsnames.ora 文件如下：
[oracle@localhost admin]$ cat 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.
REMOTE_ORCL =
(DESCRIPTION =
(ADDRESS_LIST
=
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.56.103)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =
orcl)
)
)
ORCL =
(DESCRIPTION
=
(ADDRESS = (PROTOCOL = TCP)(HOST =
localhost.localdomain)(PORT = 1521))
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
orcl)
)
)
[oracle@localhost admin]$
此处建立的连接到机器2的本地服务名为 remote_orcl。
步骤3：机器1上，给予用户scott（将来从此用户来作为dblink的访问者）建立public dblink的权限。
SQL> alter user scott account unlock;
User altered.
SQL>
alter user scott identified by scott;
User altered.
SQL>
SQL> grant CREATE PUBLIC DATABASE LINK, DROP PUBLIC DATABASE LINK to
scott;
Grant succeeded.
SQL>
步骤4：建立dblink，通过dblink，可以访问机器2上的 hr schema。
[oracle@localhost ~]$ sqlplus scott/scott
SQL*Plus: Release 11.2.0.1.0
Production on Fri Mar 14 11:26:35 2014
Copyright (c) 1982, 2009,
Oracle. All rights reserved.
Connected to:
Oracle Database
11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the
Partitioning, OLAP, Data Mining and Real Application Testing
options
SQL>
SQL> create public database link remote_mydb connect to hr identified by
hr using ‘remote_orcl‘;
Database link created.
SQL>
此处建立的dblink名称为 remote_mydb。连接到机器2的hr schema，identified by hr 中的hr，是机器2上hr用户的口令。
步骤5：在机器1上，scott用户通过 dblink 访问机器2的hr schema的表：
SQL> select * from hr.countries@remote_mydb;
CO
COUNTRY_NAME
REGION_ID
-- ---------------------------------------- ----------
AR
Argentina
2
AU
Australia
3
BE
Belgium
1
BR
Brazil
2
CA
Canada
2
CH
Switzerland
1
CN
China
3
DE
Germany
1
DK
Denmark
1
EG
Egypt
4
FR
France
1
...
25 rows selected.
SQL>