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;
------------------------------ --------------------------------------
SQL> sho user
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:

[ ~]$ cat
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
EXECUTE IMMEDIATE 'drop database link $2';
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:

[]$ sh test test_db_link
SQL*Plus: Release 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> 2 3 4 5
Procedure created.
PL/SQL procedure successfully completed.
Procedure dropped.
SQL> " DB Link After Drop"
no rows selected
Disconnected from Oracle Database 11g Enterprise Edition Release - 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 🙂





15 replies

  1. 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!

  2. If you are going for finest contents like myself, just pay a
    quick visit this site everyday because it presents feature contents, thanks

  3. What’s up i am kavin, its my first time to commenting anywhere, when i read this paragraph i
    thought i could also create comment due to
    this good piece of writing.

  4. Thanks for another wonderful post. Where else could anyone get that kind of information in such an ideal way of writing? I’ve a presentation next week, and I am on the look for such information.

  5. Thanks for your personal marvelous posting! I quite enjoyed reading it, you are a great author.I will be sure to bookmark your blog and will eventually come back later on. I want to encourage you continue your great job, have a nice afternoon!

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s