Oracle DBLINk的使用

Oracle DBLINK

作用:将多个不同地点的服务器的oracle数据库逻辑上看成一个数据库,也就是说在一个数据库中可以操作另一个远程的数据库中的对象。
语法:
CREATE [PUBLIC] DATABASE LINK dblink CONNECT TO user IDENTIFIED BY password USING ‘connect_string’;
DROP [PUBLIC] DATABASE LINK dblink;
    注意:你必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的权限（可用sys身份去分配）,另外,在你要连接的数据库上的权限.
grant CREATE DATABASE LINK to hr;

参数说明:
    dblink: 你所创建的database link的名字,
    user和password:要连接的数据库的用户名和密码
    connect_string:可以是经过Net Manager配置的(tnsnames.ora)且经测试可以连接的服务名，不过也更直接用tnsnames里的字符串：(DESCRIPTION =

(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME =orcl) )

通过SHOW PARAMETER GLOBAL_NAMES，可以查看到其值是FALSE或者TRUE。

一）对于GLOBAL_NAMES=FALSE的情况，则DBLINK的名称可以自定义，相关的过程如下：
实现从本地数据库连接到远端数据库服务器：
远程服务器要配置监听并且启动它
本地服务器要配置tnsnames

 实现在本地服务器上连接到远程服务器数据库：

在本地数据库上，创建连接：
1、 链接字符串即服务名，首先在本地配置一个服务名，地址指向远程的数据库地址,当然也直接写的连接字符串。
2、创建数据库链接（前提是已分配相应权限），
SQL> grant CREATE DATABASE LINK to hr;

Grant succeeded.

SQL> CREATE DATABASE LINK LinkRemoteTestDB CONNECT TO hr IDENTIFIED BY hr USING 'test';

Database link created.

当然也可以直接写连接字符串
SQL>create database link LinkRemoteTestDB2 connect to hr identified by hr
using 'TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )';
则创建了一个以hr用户和TEST数据库的链接LinkRemoteTestDB.

3.使用database link来查询远程HR schema下的testdblink 表信息
SQL> select * from testdblink@LinkRemoteTestDB;

        ID NAME
---------- ----------------------------------------
         1 Watson
         2 John
通过执行select * from testdblink@LinkRemoteTestDB; 进行测试，结果OK

 

二）对于GLOBAL_NAMES = TRUE的情况，数据库链接（DATABASE LINK）的名字必须和数据库的名字相同：
在本地服务器上执行下面语句使GLOBAL_NAMES=TRUE：
SQL>ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
再查询时，会有如下的错误：
SQL> select * from testdblink@LinkRemoteTestDB;
select * from testdblink@LinkRemoteTestDB
                         *
ERROR at line 1:
ORA-02085: database link LINKREMOTETESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM
connects to TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

 

登录远端数据库，通过执行
SQL>SELECT * FROM GLOBAL_NAME;得到其数据库全名为TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM
用原方式SQL> CREATE DATABASE LINK LinkRemoteTestDB CONNECT TO hr IDENTIFIED BY hr USING 'test';创建过程不会出错，但执行“select * from

testdblink@LinkRemoteTestDB;”的时候，就会出现ORA-02085: database link LINKREMOTETESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM
connects to TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM的错误了
所以需要采用下面的方式创建DBLINK:
SQL> create database link TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to HR identified by HR using 'TEST';

Database link created.再次执行
SQL> select * from testdblink@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;

        ID NAME
---------- ----------------------------------------
         1 Watson
         2 John

SQL> UPDATE testdblink@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM t set t.name='WatsonModified' where id=1;

1 row updated.

SQL> select * from testdblink@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;

        ID NAME
---------- ----------------------------------------
         1 WatsonModified
         2 John

执行成功了！！！！！

DBLINK其他相关的知识：
1、查看所有的数据库链接
SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';
SQL>select * from dba_db_links;

2、删除数据库连接
SQL> drop database link LinkRemoteTestDB;

Database link dropped.