ORA-02082: a loopback database link must have a connection qualifier

One more for today,

Here is the error, this database link should point to production db,

SQL> create database link PRODDB connect to admin identified by admin using 'PROD';
create database link MQDB connect to strmadmin identified by strmadmin using 'PROD'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

SQL> drop database link proddb;
drop database link proddb
*
ERROR at line 1:
ORA-02024: database link not found

Weird, no rows in dba_db_links

Lets check, whether my global_name set to true and what value it is

SQL> Show parameter global_name

global_name true

SQL> show parameter instance_name

instance_name      repdb

SQL> show parameter db_name

db_name               proddb

SQL> select * from global_name;

global_name

----------------

proddb

Well, this is it, my global_name set to proddb which is acting as a loopback database link(not sure whether its exists or not) looking self itself and unable to identify. So be aware if your db_name and global_name is true you may encounter this.

To fix this,

SQL> alter database rename global_name to repdb; –> to my instance name

Database altered.

SQL> drop database link proddb;
drop database link proddb
*
ERROR at line 1:
ORA-02024: database link not found

Try to create the database link

SQL> create database link PRODDB connect to admin identified by admin using 'PROD';

Database link created.