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
Make DBA Life Easy 