So while back there was an error while trying to drop a public database link
SQL> drop public database link "BOB.DATABASE_LINK_1"; drop public database link "BOB.DATABASE_LINK_1" * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-31600: invalid input value DATABASE LINK for parameter OBJECT_TYPE in function GET_DDL ORA-06512: at "SYS.DBMS_METADATA", line 5805 ORA-06512: at "SYS.DBMS_METADATA", line 8344 ORA-06512: at line 14
And the even strange part is that executing DBMS_METADATE.get_ddl worked against the database link
SQL> SELECT dbms_metadata.get_ddl('DB_LINK','BOB.DATABASE_LINK_1','SYS') FROM dual; CREATE DATABASE LINK "BOB.DATABASE_LINK_1" CONNECT TO "NYU" IDENTIFIED BY VALUES '*******' USING 'ORCL';
The solution relied on MOS note Cannot drop a database link after changing the global_name ORA-02024 (Doc ID 382994.1) all though the symptoms in the note were not a match to the problem above the solution did the trick.
Which is to backup the table sys.link$ as CTAS and delete the row corresponding to the database link which you need to drop.
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$:
Delete the DBLINK as follows:
$sqlplus /nolog connect / as sysdba SQL> delete sys.link$ where name='db_link_name>'; SQL>commit;
Verify if the operation was correctly proceeded:
select db_link, username, host from user_db_links;
Hope it helps!