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 works.
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.
ok, but what if I can’t change parameter global_names ?
Then you should create a database link with the same name as the remote global name.
> I create DB link from Source DB to select in Target DB and I found ORA-02085: error.
> I solve this ORA-02085 issue by create db link name as same as SID target DB.
** my source param DB
SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean TRUE
** my target Param DB
SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean FALSE
Good for you!
Hello, I have this error and I’m connecting from DBCS instance to non-DBCS OCI instance, however the db_link name matches the target database name, and global_names is already false:
Enter the parm whose value you wish to see: global_nam
NAME VALUE
—————————— ——————————————————————————–
global_names FALSE
Any idea why it’s still unhappy?
Maybe you should check the definition of your db link.