无法删除DBLINK ORA-02024 database link not found

Cannot drop a database link after changing the global_name ORA-02024 (Doc ID 382994.1)

In this Document

Symptoms

Changes

Cause

Solution

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.6 and later

Information in this document applies to any platform.

***Checked for relevance on 14-Nov-2014***

Not able to drop a database link after changing the global_name of the database 

Earlier global_name had did not have domain name attached to it.

The newly added global_name has a domain name attached to it. 

When trying to drop the database link after this change throws the following error 

ORA-02024: database link not found 

But database link is present and the query on user_db_links displays the value 

Example:- 

SQL> select * from global_name; 

GLOBAL_NAME 

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

DB10GR2 

SQL> create database link l1 connect to scott identified by tiger; 

Database link created. 

SQL> select db_link from user_db_links; 

DB_LINK 

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

L1 

SQL> alter database rename global_name to DB10GR2.WORLD; 

Database altered. 

SQL> select * from global_name; 

GLOBAL_NAME 

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

DB10GR2.WORLD 

SQL> drop database link l1; 

drop database link l1

ERROR at line 1: 

ORA-02024: database link not found 

Even if the global_name is changed back to the original value, the same errors 

occurs.

Initially when a database is created without domain in the global name, null will used from domain as opposed to .world in earlier releases 

Later on when the global_name is altered to contain the domain part also, this domain remains even when the global_name is altered back a name without domain name 

Example :- 

SQL> select * from global_name; 

GLOBAL_NAME 

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

DB10GR2 

SQL> alter database rename global_name to DB10GR2.WORLD; 

Database altered. 

SQL> select * from global_name; 

GLOBAL_NAME 

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

DB10GR2.WORLD 

SQL> alter database rename global_name to DB10GR2; 

Database altered. 

SQL> select * from global_name; 

GLOBAL_NAME 

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

DB10GR2.WORLD

The only option left to correct this is to update the base table props$

 

There are two solutions:

1.  Update sys.link$ and rename the name column to make it match so that the command to drop public database link works.

    1.1. Take a complete consistent backup of the database 

    1.2. Execute the following: 

$sqlplus /nolog 

connect / as sysdba 

SQL> update props$ set value$ = '<globalname without domain>' where name ='GLOBAL_DB_NAME'; 

SQL>commit;

    1.3 Drop the database link:

        1.3.1. Connect as the schema user that owns the DBLINK and try to drop it.

If you still get ORA-2024: database link not found , that means the domain name is in your cache and needs to be cleared.

        1.3.2. Flush shared pool thrice and retry drop database link.

alter system flush SHARED_POOL;

alter system flush SHARED_POOL;

alter system flush SHARED_POOL;

        1.3.3. If step b doesn't help, you need to bounce your database and try to drop the database link.

        1.3.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

 

2. The second solution consists on deleting the database link directly from sys.link$:

        2.1. Take a complete consistent backup of the database or use CTAS can be used to backup sys.link$:

   

$sqlplus /nolog

connect / as sysdba

SQL> create table backup_link$ as select * from sys.link$:

 

        2.2. Delete the DBLINK as follows:

$sqlplus /nolog

connect / as sysdba

SQL> delete sys.link$ where name='db_link_name>';

SQL>commit;

        2.3. Verify if the operaion s correctly proceeded:

select db_link, username, host from user_db_links;

 

Reference

Bug 3675157 - ORA-02024: CANNOT DROP DBLINK AFTER THE GLOBAL_NAME PARAMETER IS CHANGED.

Still have questions ?

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle Support  Streams and Distributed Database Community