ORA-02085: database link DB_LINK connects to SOURCE_DB

After successfully created the database link called “DB_LINK”, the try to access remote table with the following errors:

SQL>  select  count(*) from remote_user.table_name@db_link;
 select  count(*) from REMOTE_USER.TABLE_NAME@DB_LINK                                                         *
ERROR at line 1:
ORA-02085: database link DB_LINK connects to SOURCE_DB

 $ oerr ora 02085
02085, 00000, "database link %s connects to %s"
// *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.
//

SQL>  show parameter global_names

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

SOLUTION

1)Reset parameter global_names to FALSE, and recreate the database link again with any name you want.

SQL> alter system set global_names=false;

SQL> Create public database link DB_link connect to ....

SQL>  select  count(*) from remote_user.table_name@db_link;

  COUNT(*)
----------
      1000

2) Create database link with the same name as source database / PDB GLOBAL_NAME.

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------
SOURCE_DB

SQL> Create public database link SOURCE_DB connect to ....

SQL>  select  count(*) from remote_user.table_name@source_db;

  COUNT(*)
----------
      1000

For connecting to PDB, you need be in the PDB to get right GLOBAL_NAME:

SQL> show con_name;

CON_NAME
------------------------------
SOURCEDBPDB

SQL>  select * from global_name;

GLOBAL_NAME
--------------------------------------------
SOURCEDBPDB

SQL>Create public database link SOURCEDBPDB connect to ....

SQL>  select  count(*) from remote_user.table_name@sourcedbpdb;

  COUNT(*)
----------
      1000