创建dblink时，遇到ORA-02085
SQL> create public database link xuh_link connect to xuh identified by xuh using 'orcl';

Database link created.

SQL> col db_link for a20
SQL> col host for a20
SQL> set line 200
SQL> select * from all_db_links;

OWNER                          DB_LINK              USERNAME                       HOST                 CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
PUBLIC                         XUH_LINK             XUH                            orcl                 24-AUG-15

SQL> select count(*) from xuh@xuh_link;
select count(*) from xuh@xuh_link
                          *
ERROR at line 1:
ORA-02085: database link XUH_LINK connects to ORCL

查看相关资料有两种方式解决上述问题
SQL> show parameter global_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_names                         boolean     TRUE

第一种：将上述本地数据库global_names参数设置为false

SQL> alter system set global_names=false scope=both;

System altered.

SQL> select count(*) from xuh@xuh_link;

COUNT(*)
----------
    150422

第二种：将database link name和远程数据库中的global_name设置成一致
SQL> select * from all_db_links;

OWNER                          DB_LINK              USERNAME                       HOST                 CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
PUBLIC                         XUH_LINK             XUH                            orcl                 24-AUG-15

SQL> create public database link orcl connect to xuh identified by xuh using 'orcl';

Database link created.

SQL> select * from all_db_links;

OWNER                          DB_LINK              USERNAME                       HOST                 CREATED
------------------------------ -------------------- ------------------------------ -------------------- ---------
PUBLIC                         XUH_LINK             XUH                            orcl                 24-AUG-15
PUBLIC                         ORCL                 XUH                            orcl                 24-AUG-15

SQL> select count(*) from xuh@xuh_link;
select count(*) from xuh@xuh_link
                         *
ERROR at line 1:
ORA-02085: database link XUH_LINK connects to ORCL

SQL> select count(*) from xuh@orcl;

COUNT(*)
----------
    150422

SQL>

ITPub博客

建DBLINK时遇ORA-02085

最新文章