Skip to Main Content

Dynamic drop database link

User341075Aug 30 2011 — edited Oct 5 2012
Hi,

As user SYS i need to Dynamically DROP all the database link in the Database.
I cant drop private database link .
For example i have few database links under APPS schema that i would like to drop.
Please note that it should be done by SYS user
SQL> declare
  2  
  3  cursor c is
  4  select * 
  5  from dba_db_links;
  6  
  7  
  8  begin
  9  for c_rec in c loop
 10  
 11      if c_rec.owner = 'PUBLIC' then
 12          execute immediate ' drop public database link '||'"'||c_rec.db_link||'"';
 13      else
 14          dbms_output.put_line (' drop database link '||'"'||c_rec.db_link||'"');
 15           execute immediate ' drop database link '||'"'||c_rec.db_link||'"';
 16      end if;      
 17  end loop;
 18  
 19  end;
 20  /

drop database link "APPS_TO_APPS"
declare
*
ERROR at line 1:
ORA-02024: database link not found
ORA-06512: at line 15
Thanks
Comments
Post Details
Added on Aug 30 2011
12 comments
3,931 views