Need to drop a database link in another user’s schema? This is how.
define link_name='SOME_DBLINK'
define link_owner='SOME_OWNER'
declare
cmd varchar2(2000);
drop_proc_name varchar2(100);
begin
dbms_output.enable(null);
SYS.DBMS_RANDOM.seed(to_number(TO_CHAR(SYSDATE,'HH24MISS')));
drop_proc_name := '&&link_owner' || '.drop$$' || trunc(SYS.DBMS_RANDOM.VALUE (10000, 99999));
dbms_output.put_line('PROC_NAME: ' || drop_proc_name);
cmd := 'create or replace procedure ' || drop_proc_name || ' as begin execute immediate ''drop database link &&link_name''; end;';
dbms_output.put_line('EXECUTING: ' || cmd);
execute immediate cmd;
cmd := 'begin ' || drop_proc_name|| '; end;';
dbms_output.put_line('EXECUTING: ' || cmd);
execute immediate cmd;
cmd := 'drop procedure ' || drop_proc_name ;
dbms_output.put_line('EXECUTING: ' || cmd);
execute immediate cmd;
dbms_output.put_line('DONE.');
exception when others then
dbms_output.put_line(sqlerrm);
end;
/