ORA-02085: database link MYDB_LINK connects to MYDB

If you create a database link and get the ORA-02085 error, likely your database parameter global_names is set to TRUE.

SQL> CREATE DATABASE LINK MYDB_LINK connect to joe identified by test1 using 'MYDB_PROD';

Database link created.

SQL> select count(*) from customer@MYDB_LINK;
select count(*) from customer@MYDB_LINK
                                     *
ERROR at line 1:
ORA-02085: database link MYDB_LINK connects to MYDB

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

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------
MYDB

To solve this issue, you need to either set global_names to FALSE at the session level:

SQL> alter session set global_names=false;

Session altered.

SQL>  select count(*) from customer@MYDB_LINK;

  COUNT(*)
----------
     18180

or use the global name of remote database as the DB link’s name. However if your local database has the same global name as the remote database, like a Test database and a Production database both have the identical settings, you need to use a qualifier for the link name or you will see ORA-02082.

SQL> CREATE DATABASE LINK MYDB connect to joe identified by test1 using 'MYDB_PROD';
CREATE DATABASE LINK MYDB connect to joe identified by test1 using 'MYDB_PROD';
                           *
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

With the qualifier “@MYDB_PROD” appended, the error is gone.

SQL> CREATE DATABASE LINK MYDB@MYDB_PROD connect to joe identified by test1 using 'MYDB_PROD';

Database link created.

SQL> select count(*) from customer@MYDB@MYDB_PROD;

  COUNT(*)
----------
     18180