ORA-02024: database link not found

If you are trying to drop a database link after changing the global_name of the database , you usually end up with ora-02024: database link not found

 

But when you query user_db_links  view you found DB link exists .

 

 

Possible root cause would be

 

Initially when we create a database without domain in the global name, a null value will be used from domain as opposed to .world in Oracle releases 9i and before.

Later on when the global_name is altered to contain the domain part that is “world”,   this domain remains even when the global_name is altered back a name without domain name.

 

 

In order to drop the desired database link 

 

I would recommend to try this solution in Development DB then in Test  

And with a cold backup in production. 

 

 

1)Conn sys as sysdba

 

 

2) find out current global_name value using below sql

 

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------------------------------
THESIMPLEORACLE.WORLD

 

Find out the value  of  GLOBAL_NAME FROM props$ TABLE

 

Select name, value$ from  props$ where name = 'GLOBAL_DB_NAME';

 

NAME

------------------------------

VALUE$

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------

----------------------------

GLOBAL_DB_NAME

THESIMPLEORACLE.WORLD

 

 

Step 3) Update global name value to desired name with our domain name

 

Syntax :

update props$ set value$ = '' where name ='GLOBAL_DB_NAME';

 

 

Example :

 

update props$ set value$='THESIMPLEORACLE’  where name like 'GLOBAL_DB_NAME';

 

SQL>commit;

 

 

3)Then connect as the schema user that owns the DBLINK and try to drop it.

4) Once the database link is dropped, the global_name can be changed back to the
desired name containing domain part using the alter database rename global_name statement