Dropping Database link in another schema

Oracle database does not allow to drop database link owned by another user. As a DBA user, to drop database link in another schema, here are steps to follow.

1. Create a procedure in another schema in which database link exists
2. The procedure should have statement to drop database link
3. Execute the procedure
4. Drop the procedure

These steps can be written as PL/SQL anonymous block like below. 

declare

          schema_name varchar2(10) := 'MURTY';

     -- db_link_name should be same as value of DB_LINK column in DBA_DB_LINKS view

     db_link_name varchar2(100) := 'remote.xyz.com';

     random_proc_name varchar2(100);

     cnt number;

begin

     schema_name := upper(schema_name);

     db_link_name := upper(db_link_name);

        -- A random procedure to make sure the name of procedure should not conflict with existing procedures

     random_proc_name := schema_name || '.drdl_' || dbms_random.string('U', 20);

     select count(*) into cnt from dba_db_links where owner=schema_name and db_link=db_link_name;

 

           if ( cnt != 1 ) then

          raise_application_error(-20001, 'DB Link does not exist');

     end if;

    

          -- 1. Create a procedure in another schema in which database link exists

          execute immediate

               ' create procedure ' || random_proc_name || ' as ' ||

               ' begin ' ||

               -- 2. The procedure should have statement to drop database link

               ' execute immediate ''drop database link ' || db_link_name ||' ''; ' ||

               ' end;';

 

           begin

          -- 3. Execute the procedure

               execute immediate 'begin ' || random_proc_name || '; end;';

          exception when others then

                    execute immediate 'drop procedure ' || random_proc_name;

                     raise;

          end;

           -- 4. Drop the procedure

           execute immediate 'drop procedure ' || random_proc_name;

end;

/