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!
