Oracle DB Link

来源：互联网发布：土地确权数据汇交编辑：程序博客网时间：2024/06/11 09:10

DB LINK 即database link

1,创建DB LINK
SQL>conn hr/hr登录到源DB的hr帐户下;
SQL>create database link itods.oracle.sannet.net connect to hr identified by hr using 'itods.oracle.sannet.net';
其中第一个itods.oracle.sannet.net是DB Link的名字,相同schema下,不能有相同名字的DB Link
hr/hr是目标DB的用户名和密码
第二个itods.oracle.sannet.net是源DB tnsnames.ora配置的目标DB的连接字符串.

2,globa_names问题
当目标DB的global_names设置为True时,同一个schema下,如果有多个连接到相同目标DB的DB Link,只有DB Link名字与global_name相同那个才能
正常使用.
测试如下:
SQL> select * from dba_db_links;

OWNER      DB_LINK                             USERNAME   HOST                           CREATED
---------- ----------------------------------- ---------- ------------------------------ ---------
SYS        ITODS.ORACLE.SANNET.NET             NICK_PENG itods.oracle.sannet.net        14-FEB-12
HR         ITODS_NICK_PENG.ORACLE.SANNET.NET   NICK_PENG itods.oracle.sannet.net        15-FEB-12
HR         ITODS.ORACLE.SANNET.NET             HR         itods.oracle.sannet.net        15-FEB-12

SQL> select * from user_db_links;

DB_LINK                             USERNAME   PASSWORD   HOST                           CREATED
----------------------------------- ---------- ---------- ------------------------------ ---------
ITODS.ORACLE.SANNET.NET             NICK_PENG             itods.oracle.sannet.net        14-FEB-12

HR下面有2个DB Link,只有ITODS.ORACLE.SANNET.NET可以正常连接.

测试报错如下:
SQL> select * from provider@ITODS_NICK_PENG.ORACLE.SANNET.NET;
select * from provider@ITODS_NICK_PENG.ORACLE.SANNET.NET
                       *
ERROR at line 1:
ORA-02085: database link ITODS_NICK_PENG.ORACLE.SANNET.NET connects to ITODS.ORACLE.SANNET.NET

SQL> select * from provider@ITODS.ORACLE.SANNET.NET;

        ID NAME    ADDRESS
---------- ------ --------------------------------------------------------------------
        1 DELL    Xiamen Fujian China

3,权限
SQL>grant create database link to hr;
SQL>drop database link itods.oracle.sannet.net;

http://database.51cto.com/art/201108/281895.htm