Wednesday, November 12, 2008

ORA-02085: database link %s connects to %s

This error happens when the database parameter global_names is set to TRUE.

When this is the case, the database link need to have the same name as the global name of the remote database (the one you are trying to connect to).
eg:

SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- --------------------
global_names boolean TRUE

SQL> create database link test connect to myuser identified by mypw using 'ORA920';
Database link created.

SQL> select * from global_name@test;
select * from global_name@test
*
ERROR at line 1:
ORA-02085: database link TEST.EU.DBMOTIVE.COM connects to ORA920.EU.DBMOTIVE.COM

-----
Now, first determine the global name of the remote database.
Connect to the remote database and issue following select:

SQL> select global_name from global_name;

GLOBAL_NAME
---------------------------------------------------
ORA920.EU.DBMOTIVE.COM

-------

Back to the database where we're trying to create the database link to ORA920

SQL> drop database link test;

Database link dropped.

SQL> create database link ORA920 connect to myuser identified by mypw using 'ORA920';

Database link created.

SQL> select * from global_name@ora920;

GLOBAL_NAME
-------------------------------------------------------------------
ORA920.EU.DBMOTIVE.COM

When you need more than 1 database link to connect to the remote database link, you'll have to use a qualifier:
SQL> create database link ORA920@MYUSER connect to myuser identified by mypw using 'ORA920';

Database link created.

SQL> select * from global_name@ora920@myuser;

GLOBAL_NAME
-------------------------------------------------------------------
ORA920.EU.DBMOTIVE.COM

No comments:

search engine

Custom Search