Database link cannot be dropped because of ORA-02024

When dropping database link got following error:
ORA-02024: database link not found.

SQL> select * from user_db_links;

DB_LINK           USERNAME      PASSWORD   HOST        CREATED
----------------- ------------- ---------- ----------- ---------
REFRESH_DBLINK    SYSTEM                   DBPROD     15-OCT-13

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

Can not find an existing database link? What if create a new link with same name?

SQL> create database link REFRESH_DBLINK
  2  connect to system identified by Ax356 using 'DBPROD';

Database link created.

SQL> select * from user_db_links;

DB_LINK                  USERNAME      PASSWORD   HOST        CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK           SYSTEM                   DBPROD     15-OCT-13
REFRESH_DBLINK.WORLD     SYSTEM                   DBPROD     29-JAN-15

The name of new database link is appended with '.WORLD'. Did more test and found that all new created database link will be added suffix '.WORLD' in the name. Try DROP DATABASE LINK to see what is going to happen:

SQL> drop database link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

DB_LINK                  USERNAME      PASSWORD   HOST        CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK           SYSTEM                   DBPROD     15-OCT-13

See, I was dropping REFRESH_DBLINK, but REFRESH_DBLINK.WORLD was dropped. I always get REFRESH_DBLINK.WORLD dropped no matter whether I include '.WORLD' in the DROP DATABASE LINK command. Why did we get the weird term '.WORLD'? It was popular long time ago as default database domain and often found in global_name & db_domain of the database:

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      DBPROD

SQL> show parameter db_domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
DBPROD.WORLD

It does be a part of database global_name. Does domain part in global_name affect database link name space? Unfortunately, it does. Delete the domain part from global_name:

 

SQL> alter database rename global_name to DBPROD;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------
DBPROD.WORLD

Interesting, the ALTER DATABASE command does not help. Let us manually change global_name bypass ALTER DATABASE command,
Note: Take a complete consistent backup of the database before you continue:

SQL> conn / as sysdba
Connected.

SQL> select name,value$ from props$ where GLOBAL_name='GLOBAL_DB_NAME';

NAME                           VALUE$
------------------------------ --------------------------------------------------
GLOBAL_DB_NAME                 DBPROD.WORLD

SQL> update props$ set value$='DBPROD' where  name='GLOBAL_DB_NAME';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from  global_name;

GLOBAL_NAME
---------------------
DBPROD

Oracle never suggests direct update on props$. We are not supposed to do that. Hmmm, today is special :)

Drop the database link:

SQL> connect system

SQL> drop datbaetabase link REFRESH_DBLINK;

Database link dropped.

SQL>  select * from user_db_links;

no rows selected

Finally, database link is really dropped.