How to drop other schema's Database link

There are multiple methods to create or drop other schema's db link




1) using 'connect through' method


SQL> alter user <targetuser> grant connect through oracle;


User altered.


SQL> grant create database link to <targetuser>;


Grant succeeded.


SQL>conn /


SQL> conn [<targetuser>]


Connected.


SQL>


create or drop


SQL> alter user <targetuser> revoke connect through ops$oracle;


User altered.


SQL> revoke create database link from <targetuser>;



2) Using a procedure


We could not 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 in under
the same schema
>drop database link perf_mon.sts;
drop database link perf_mon.sts
                   *
ERROR at line 1:
ORA-02024: database link not found

>create database link perf_mon.sts;
Database link created.
so some of dbas either reset the password to logon or logon from the same schema to drop the db links.
Would like to share  a small script to drop the database links from sysdba without resetting the schema password or login into the schema.
This will be useful during our UAT refresh to drop the production links sitting under different schemas.

================drop_schema_dblink.sh====================================
username=$1
db_link_name=$2

sqlplus /nolog <<!
spool $1_$2_drop_dblink.log
conn / as sysdba
prompt " DB_LINK_STAT  Before Drop"
select * from dba_db_links where owner='$1' and db_link='$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_STAT  After Drop"
select * from dba_db_links where owner='$1' and db_link='$2' ;

!
==========================================================
This can be achieved using various other option like scheduler job/ dbms_job. I feel the procedure is quite simple and straight forward.
==========

SQL> conn perf_mon/perf_mon
Connected.
SQL> create database link remote ;
Database link created.
SQL> conn / as sysdba
Connected.
SQL> drop database link perf_mon.remote;
drop database link perf_mon.remote
                   *
ERROR at line 1:
ORA-02024: database link not found

$ ./drop_schema_dblink.sh PERF_MON REMOTE
SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 4 12:04:38 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
SQL> SQL> Connected.
SQL> " DB_LINK_STAT     Before Drop"
SQL>
OWNER
------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
---------
PERF_MON
REMOTE

OWNER
------------------------------
DB_LINK
--------------------------------------------------------------------------------
USERNAME
------------------------------
HOST
--------------------------------------------------------------------------------
CREATED
---------
04-MAR-13

SQL> SQL>   2    3    4    5
Procedure created.
SQL>
PL/SQL procedure successfully completed.
SQL>
Procedure dropped.
SQL> SQL> " DB_LINK_STAT        After Drop"
SQL> SQL>
no rows selected
SQL> SQL> SQL>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option