Oracle中实现dblink的作法

基本环境:

机器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>

Oracle中实现dblink的作法,布布扣,bubuko.com

Oracle中实现dblink的作法

上一篇:Gentoo linux安装mongoDB


下一篇:服务器环境搭建系列(四)-mysql篇