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

ORA-02082

When I tried to create a database link, it threw ORA-02082.

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

Rationale

The above error indicates that this name of database link conflicts with the global database name and could link to itself. In my case, I don't intend to loopback to itself. I want a database link which points to a remote database. Just coincidentally, they are in the same name.

This is because the database 12c reserves a loopback database link with same name as the database global name without explicitly creating a database link. Therefore, you can't find it in the view dba_db_links

Let's go further and see the global name of this database.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
BOSTON

SQL> show parameter global_names

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
global_names                         boolean
FALSE

As you can see, our global name does link to itself and I don't need to specify the fully-qualified database name in the statement because GLOBAL_NAMES is FALSE.

Solutions

Now, we have two choices, one is to choose another link name for the remote database, which is pretty easy. The other is to rename the global name of the local database. I choose the latter one, because the link name is tied to the application. And I don't want to beg them for the change.

SQL> alter database rename global_name to CHICAGO;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
CHICAGO

Now, we can create this database link without ORA-02082. It's the same statement as the one in the beginning of this post.

SQL> create public database link BOSTON connect to SCOTT identified by SCOTT using 'BOSTON';

Database link created.

SQL> select name from v$database@BOSTON;

NAME
---------
BOSTON

2 thoughts on “How to Resolve ORA-02082: a loopback database link must have a connection qualifier”

  1. You can actually give the dblink name an unique identifier and make a connection without changing global names.

    create database link dbname@uniqueident connect to system identified by manager using ‘db-tst.local:1526/dbname’;

    select * from dual@dbname@uniqueident;

Leave a Reply

Your email address will not be published. Required fields are marked *