Many a time we DBA’s face issues as we cannot drop other schema’s database link with schema qulalifier Since dot is allowed in the database link name. Oracle check for a dblink with the given name under the same schema. For performing this task we need to connect to owner schema & drop it.

SQL> select owner,db_link from dba_db_links;
OWNER DB_LINK
------------------------------ --------------------------------------
TEST TEST_DB_LINK
SQL> sho user
USER is "SYS"
SQL>
SQL> drop database link test.test_db_link;
drop database link test.test_db_link
 *
ERROR at line 1:
ORA-02024: database link not found

This post will provide u – small script to drop the database links from SYS, without resetting the schema password or login into the schema.
This will be useful after cloning UAT environment to drop the production links sitting under different schemas.

Contents of scripts are as follows:

[test@test.test.com ~]$ cat drop_schema_dblink.sh
username=$1
db_link=$2
sqlplus /nolog <<!
spool $1_$2_drop_dblink.log
conn / as sysdba
prompt " DB Link Before Drop"
set lines 100
col db_link for a30
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
CREATE or replace PROCEDURE $username.drop_db_link AS
BEGIN
EXECUTE IMMEDIATE 'drop database link $2';
END ;
/
execute $username.drop_db_link;
drop procedure $username.drop_db_link;
prompt " DB Link After Drop"
select owner,db_link from dba_db_links where owner=upper('$1') and db_link=upper('$2') ;
!

Usage of the script is very simple & is as follows:

[test@test.test.com~]$ sh drop_schema_dblink.sh test test_db_link
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 29 20:37:04 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL> " DB Link Before Drop"
SQL> SQL> SQL>
OWNER DB_LINK
------------------------------ ------------------------------
TEST TEST_DB_LINK
SQL> 2 3 4 5
Procedure created.
SQL>
PL/SQL procedure successfully completed.
SQL>
Procedure dropped.
SQL> " DB Link After Drop"
SQL>
no rows selected
SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath