How to fix when receiving ORA-02082 when trying to drop a database link?

The issue occurs when there is a db link which is a loop back database link i.e. points to itself

Example:
01:52:20 @> DROP DATABASE LINK TEST.WORLD;
DROP DATABASE LINK TEST.WORLD
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

-- Shows the global name matches the db link
01:52:44 @> select * from global_name;

GLOBAL_NAME
—————————————————————————————————-
TEST.WORLD

1 row selected.

-- So to fix the issue rename the global_name to something else
01:55:37 @> Alter database rename global_name to TEMP.XXXX;

Database altered.

-- After changing global name one is able to drop the database link and after dropping the database link you can rename the global names
01:55:58 @> DROP DATABASE LINK TEST.WORLD;

Database link dropped.

01:56:08 @> Alter database rename global_name to TEST.WORLD;

Database altered.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s