ORA-02085: Database link created successfully but does not work

I want to create database link on base a database. It means link from database to itself.

My database name is: MYDB

SQL> select name from v$database;

NAME
---------
MYDB

SQL>

--Now try to create database link via hr user.

SQL> create database link mydb_mydb connect to hr identified by hr using 'mydb';

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 MYDB_MYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to
MYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM


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 'mydb';

Database link created.

--No check database link

SQL> select * from dual@mydb_mydb;

D
-
X

SQL>

It is work.

What else if global_names`s TRUE and I try to create db link with same same of SID ?


SQL> create database link mydb connect to hr identified by hr using 'mydb';
create database link mydb connect to hr identified by hr using 'mydb'
                          *
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier


To prevent this kind of error rename global name.


SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
MYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM

SQL> alter database rename global_name to TESTDB.TESTDOMAIND;

Database altered.

SQL> create database link mydb connect to hr identified by hr using 'mydb';

Database link created.

DB link created, bu still does not work.


SQL> select * from dual@mydb;
select * from dual@mydb
                   *
ERROR at line 1:
ORA-02085: database link MYDB.TESTDOMAIND connects to TESTDB.TESTDOMAIND


Now update global_name`s again and check db link.


SQL> alter database rename global_name to MYDB;

Database altered.

SQL> select * from dual@mydb;

D
-
X