ORA-02085错误解决过程！

       今天看EYGLE的文章：http://www.eygle.com/archives/2005/06/oraclessoeaeaeo.html（Oracle高级复制的创建配置步骤-Step by Step），里面讲到了：本例的先决条件:你需要设置好相应的参数,job_queue_processes需要大于0,global_name=true,并且建立相应的db link.

      于是我这个菜鸟便检查主数据库job_queue_processes和global_names参数，并通过alter system set global_names=true;修改global_names参数，没有任何问题！

   然后在主数据库创建dblink：

 create database link test_link
  connect to myuser identified by pass
  using 'mydb2';

创建过程也没有问题！但是使用dblink进行查询就出错了：

SQL> select count(*) from town@test_link;

select count(*) from town@test_link

ORA-02085: database link TEST_LINK connects to STIOMDB

SQL>

经过在网上搜看别人的帖子并测试发现：

当global_name参数设置为true，则dblink必须命名为和在目标数据库如下查询出的结果一致：

 select * from global_name，例如：

在目标数据库执行查询：

 

SQL>  select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
mydb

SQL>

 

则修改dblink：

 create database link mydb  connect to myuser identified by pass  using 'mydb2';

然后执行查询：

SQL> select count(*) from town@mydb;

  COUNT(*)
----------
     13507

SQL>

 

具体可参考如下描述:(摘自：http://www.itpub.net/364993.html)

 

When the source database initialization parameter GLOBAL_NAMES is set to true, the

database link name must match the target database global name as it exists in the GLOBAL_NAME

view in the data dictionary.

The GLOBAL_NAME can be determined by logging in to the database with system privileges and issuing the following command:

SQL>Select * from global_name;

Additionally, if you do not specify the domain portion of the dblink name in the create statement, Oracle automatically qualifies the link name with the domain of the SOURCE database global name view.

Check the contents of ALL_DB_LINKS for the fully qualified link name.

For example, if you defined a database link in PROD.ORACLE.COM to connect to target instance TEST.WORLD.COM in the following manner:

SQL>Create public database link TEST connect to userid identified by password using ?test?;

SQL>select * from tablename@TEST;

This select would yield the following error:

ORA-2085 "database link TEST.ORACLE.COM connects to TEST.WORLD.COM"

The correct syntax for defining the link would be:

SQL>Create public database link TEST.WORLD.COM connect to userid identified by password using “test”;

SQL>select * from [e-mail];

Would yield desired result.

It is possible to alter the GLOBAL_NAME table so that the domain portion of both SOURCE and TARGET global names are identical. This would eliminate the need to include the domain in the create database link statement.

In the above example, we could alter the GLOBAL_NAME of TEST.WORLD.COM in the following manner:

Login to TEST with system privileges and issue:

SQL>alter database rename global_name to TEST.ORACLE.COM;

Now, the create database link statement could also be changed.

Login to PROD.

SQL>create public database link TEST connect to userid identified by password using ?test?;

A database link would be defined in ALL_DB_LINKS as TEST.ORACLE.COM.

SQL>select * from tablename@TEST;

This would yield the desired result.