Sql query to drop db links of other users

 

Use the below query to generate PL/SQL procedure to drop db link of other users

set lines 800
set echo off
set feedb off
set termout off
set heading off
SET VERIFY off
set trimspool on
set long 32766
set longchunksize 120
select '--------------------------'||owner||'----'||db_link||'---------------------------------------------------------'||
chr(10)||
chr(10)||
'CREATE OR REPLACE PROCEDURE '||OWNER||'.drop_db_link AS '||
chr(10)||
'BEGIN '||
chr(10)||
'   EXECUTE IMMEDIATE ''DROP DATABASE LINK '||db_link || ' '' '||';'||
chr(10)||
'END drop_db_link ;'||
CHR(10)||
'/'||
chr(10)||
chr(10)||
'exec '||owner||'.DROP_DB_LINK;'||
CHR(10)||
'DROP PROCEDURE '||OWNER||'.drop_db_link ;'||
chr(10) as script
FROM dba_db_links ORDER BY 1; 

Output of the above query will be similar to below.

                         --------------------------USER_SCHEMA----USER_DB_LINK---------------------------------------------------------

CREATE OR REPLACE PROCEDURE USER_SCHEMA.select_db_link AS
BEGIN
   EXECUTE IMMEDIATE 'DROP DATABASE LINK USER_DB_LINK ' ;
END select_db_link ;
/

exec USER_SCHEMA.select_db_link;
DROP PROCEDURE USER_SCHEMA.select_db_link ;

Run the above statement to drop the db link.