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

I got ” ORA-02082: a loopback database link must have a connection qualifier ”  error in Oracle database.

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

Details of error are as follows.

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

Cause: Attempt to create a database link with the same name as the current database.

Action: A loopback database link needs a trailing qualifier, that is if your db_link name
 is MYDB.ORACLE.COM@INST1 - then '@INST1' is the qualifier and 'MYDB.ORACLE.COM' is the 
current database name

When trying to drop a database link (DBLink) the following error occurs:

SQL> drop public database <dblink_name.domain>;
drop public database link <dblink_name.domain>
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

a loopback database link must have a connection qualifier

This ORA-02082 error is related with the Attempt to create a database link with the same name as the current database.

A loopback database link needs a trailing qualifier, that is if your db_link name is MYDB.ORACLE.COM@INST1 – then ‘@INST1’ is the qualifier and ‘MYDB.ORACLE.COM’ is the current database name.

You have a database that was cloned from another database on a different machine.

Initially, you had a different global_name, but changed it.
You can delete the database link on the original database, but not the database link on this cloned database.

You have tried changing global_names = false/true, to no effect.

This is expected behavior when you have a database link pointing to the database you are currently on.
You will have to rename the global_name of that database before you can successfully drop the database link.

Versions 11.2, 12.1

Error: ORA-02082 a loopback database link must have a connection qualifier
---------------------------------------------------------------------------
Cause: An attempt was made to create a database link with the same name as
the current database.
Action: a loopback database link needs a trailing qualifier, for example
<dblink_name.domain>@<unique_instance_name> - the '@<unique_instance_name>' is the qualifier

Versions 9.2, 10.1, 10.2, 11.1

Error: ORA-02082 a loopback database link must have a connection qualifier
—————————————————————————
Cause: attempt to create a database link with the same name as the current
database.
Action: a loopback database link needs a trailing qualifier, e.g.
<dblink_name.domain>@<unique_instance_name> – the ‘@<unique_instance_name>’ is the qualifier

1. First find out the value of your Database:

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
<global_name>.<domain>

2. Temporarily, alter the global_name to something else.

SQL> alter database rename global_name to <new global name>;

Database altered.



SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
<new global name>.<domain>

3. Drop the public database link now:

SQL> drop public database link <dblink_name.domain>

Database link dropped.

4. After successfully dropping the link, alter the global_name back to the original name (step 1).

SQL> alter database rename global_name to <global_name>;

Database altered.


SQL> select * From global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
<global_name>.<domain>

Solution Explanation
————-
This is expected behavior when you have a database link pointing to the database you are currently on.
You have to rename the global_name of that database before you can successfully drop the database link.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )