ORA-31600: invalid input value DATABASE LINK for parameter OBJECT_TYPE in function GET_DDL When Dropping DBLink

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!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.