Monday, February 28, 2011

Database Link: ORA-02085

Today, I created Database Link on my New Database, and saw ORA-02085
SQL> create database link TESTDBLINK connect to username identified by "password" using 'TRGDB';

Database link created.

SQL> select * from tab@TESTDBLINK;
select * from tab@TESTDBLINK
*
ERROR at line 1:
ORA-02085: database link TESTDBLINK connects to TRGDB
I spent much time... then found out on My Oracle Support - Database Links: Troubleshooting ORA-2085 "database link %s connects to %s" [ID 210630.1]
When the source database initialization parameter GLOBAL_NAMES is set to TRUE, the database link name must match the target database global name as it exists in the GLOBAL_NAME
On Source Database:
SQL> show parameter GLOBAL_NAMES

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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
SRCDB
On Target Database:
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TRGDB
Idea to test:
A>
SQL> create database link TRGDB connect to username identified by "password" using 'TRGDB';

Database link created.

SQL> select * from tab@TRGDB;
< data >
B>
SQL> alter session set global_names=false;

Session altered.

SQL> select * from tab@TESTDBLINK;
< data >
just ORA-02085 error, but used much time to find out -)

4 comments:

Anonymous said...

it was really informative and useful for me. it helped me to fix the issue at the earliest by readding your blog

Surachart Opun said...

you're welcome.

Anonymous said...

Thanks..Your block is very helpful.

Anonymous said...

Thanks Sir !!

Regards
Muhammad Imtiyaz