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 qualifierRationale
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
--------------------------------------------------------------------------------
CHICAGONow, 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
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;
Thanks for your feedback.