oracle database link小结

       Database link是定义一个数据库到另一个数据库的路径的对象，database link允许你查询远程表及执行远程程序。在任何分布式环境里，database都是必要的；database link是单向访问的连接。
        在创建database link的时候，Oracle再数据字典中保存相关的database link的信息，在使用database link的时候，Oracle通过Oracle Net用用户预先定义好的连接信息，比如tnsnames访问相应的远程数据库以完成相应的工作。

1.创建db-link 
建立database link之前需要保证：从local database到remote database的网络连接是正常的，tnsping要能成功；在remote database上面有相应的访问权限。

(1)使用tns字符串
SQL> create database link pps connect to scott identified by tiger using 'orcl';

Database link created.

SQL> select * from emp@pps;  

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL         COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- -------------------- ---------------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                      20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600          300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250          500         30

[oracle@ora11g admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/product/11.2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.230)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )

 

（2）直接创建
    create database link ppt connect to scott identified by tiger
     using '(
          DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.230)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora11g)
    )
  )';

  SQL>   create database link ppt connect to scott identified by tiger
  2       using '(
  3            DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.230)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVICE_NAME = ora11g)
  9      )
 10    )';

Database link created.

 

SQL> select * from emp@ppt where rownum < 4;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL      COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- -------------------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                   20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600      300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250       500         30

2.使用同义词访问

同义词拥有如下好处：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。
 SQL> conn ppa/ppa
Connected.
SQL> create synonym ppemp  for emp@pps;

Synonym created.

SQL> select * from ppemp where rownum < 3;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

 

3.字典信息查询

SQL> col OBJECT_NAME for a50
SQL> select owner,object_name,object_type from dba_objects where object_type='DATABASE LINK';

OWNER           OBJECT_NAME                                        OBJECT_TYPE
--------------- -------------------------------------------------- -------------------
PPA             PPS                                                DATABASE LINK
PPA             PPT                                                DATABASE LINK

SQL> col host for a80
SQL> set linesize 200
SQL>select * from dba_db_links；
OWNER           DB_LINK              USERNAME             HOST                                                                        CREATED
--------------- -------------------- -------------------- -------------------------------------------------------------------------------- -------------------
PPA             PPS                  SCOTT                orcl                                                                        2014-10-29 17:45:35
PPA             PPT                  SCOTT                (                                                                           2014-10-29 17:50:50
                                                                    DESCRIPTION =
                                                              (ADDRESS_LIST =
                                                                (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.230)(PORT = 1521))
                                                              )
                                                              (CONNECT_DATA =
                                                                (SERVICE_NAME = ora11g)
                                                              )
                                                            )

也可以从link$获取相关信息
SQL> select NAME,USERID,host from link$;

NAME                 USERID                         HOST
-------------------- ------------------------------ --------------------------------------------------------------------------------
PPS                  SCOTT                          orcl
PPT                  SCOTT                          (
                                                              DESCRIPTION =
                                                        (ADDRESS_LIST =
                                                          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.230)(PORT = 1521))
                                                        )
                                                        (CONNECT_DATA =
                                                          (SERVICE_NAME = ora11g)
                                                        )
                                                      )

By DBA老菜
=========================================================================

-- The End --