I want to create database link between two databases. It means
link from database to another database. I have created the database link and
while trying to access the table through
the db link we are getting below error.
ORA-02085: database link XXXXXX connects to XXXX.WORLD
After long research came to know that it’s because of
global_names are true in the database which is causing the issue.
My database name is: SUNDB
SQL> select name from v$database;
NAME
---------
SUNDB
SQL>
--Now try to create database link via hr user.
SQL> create database link mydb_mydb connect to hr
identified by hr using 'SUNDB';
Database link created.
SQL>
--Succeed, now let`s check.
SQL> select * from dual@mydb_mydb;
select * from dual@mydb_mydb
*
ERROR at line 1:
ORA-02085: database link XXXXXXXXXX connects to XXXXXXXX.WORLD
What is that?
After researching I found:
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 the database
it connects to, or set global_names=false.
Global_names parameter indeed true on my database, lets change it.
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
global_names boolean TRUE
SQL> alter system set global_names=FALSE scope=both;
System altered.
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
global_names boolean FALSE
Drop database link which created before.
SQL> drop database link mydb_mydb;
Database link dropped.
SQL> create database link mydb_mydb connect to hr
identified by hr using 'SUNDB';
Database link created.
--No check database link
SQL> select * from dual@mydb_mydb;
D
-
X
SQL>
It is work.
No comments:
Post a Comment