How to drop other schema’s Database link using SYS user

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

 

Advertisements

10 thoughts on “How to drop other schema’s Database link using SYS user

  1. Pingback: Create Private Database Link in other Schema using SYS/SYSTEM | Adityanath's Oracle Blog

  2. Someone essentially аssiѕt to make significantly
    articles I would stɑte. That iѕ the first time I frequented yojr web page and so far?

    I amazed with the research yоu made to creɑte this aϲtuaⅼ putt up amazing.
    Wonderful process!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s