How to Resolve ORA-02085: database link SOURCE_LINK connects to SOURCE_DATABASE

ORA-02085

I created a database link without any problem. But when I tried to test the link, I got ORA-02085.

SQL> select sysdate from dual@source_link;
select sysdate from dual@source_link
                         *
ERROR at line 1:
ORA-02085: database link SOURCE_LINK connects to SOURCE_DATABASE

Let's see the content of ORA-02085.

Description

ORA-02085: database link string connects to string

Cause

a database link connected to a database with a different name. The connection is rejected.

Action

create a database link with the same name as the database it connects to, or set global_names=false.

Next, I checked the GLOBAL_NAMES.

SQL> show parameter global_names

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

Oh? GLOBAL_NAMES was set to TRUE which is not necessarily required in this database. Therefore, I changed the setting into FALSE like this:

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

System altered.

SQL> show parameter global_names

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
global_names        boolean  FALSE

I dropped then created it again.

SQL> drop database link source_link;

Database link dropped.

SQL> create database link SOURCE_LINK connect to hr identified by password using 'SOURCE_DATABASE';

Database link created.

Tried to test the new database link again.

SQL> select sysdate from dual@source_link;

SYSDATE
---------
02-DEC-16

It worked.

GLOBAL_NAMES is useful in some certain situations and would arouse other problems like ORA-02082: a loopback database link must have a connection qualifier. It's a very interesting topic for us to know database links better. So if you can't justify yourself why you use it, please turn it off (FALSE).

On the other hand, for a complex and distributed environment, you'd better to use GLOBAL_NAMES to prevent connecting to wrong databases.

2 thoughts on “How to Resolve ORA-02085: database link SOURCE_LINK connects to SOURCE_DATABASE”

Leave a Reply

Your email address will not be published. Required fields are marked *