Tag: ORA-02085

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

OR

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

ORA-02085: database link DBLINK_TEST connects to TESTDB

If parameter global_names =TRUE, the database link name has to match the remote database name.

Try to query a remote table through a database link:

SQL> select count(*) from testuser.test@dblink_test;
select count(*) from testuser.test@dblink_test;
*
ERROR at line 1:
ORA-02085: database link UDBLINK_TEST connects to TESTDB

REASON

Check database parameter global_names =TRUE. If parameter global_names =TRUE, the database link name has to match the remote database name( TESTDB).

WORKAROUND

Turn off  global_names in session level, then it should be ok.

SQL>alter session set global_names=false;
Session altered.

SQL>select count(*) from testuser.test@dblink_test;

 COUNT(*)
----------
       100