Wednesday, January 31, 2018

Drop database links from other schemas using sys

Create or replace procedure DROP_LINK(schemaName varchar2, dbLink varchar2 ) is
            plsql   varchar2(1000);
            cur     number;
            uid     number;
            rc      number;
    begin
            select
                    u.user_id into uid
           from    dba_users u
           where   u.username = schemaName;
             plsql := 'drop database link "'||dbLink||'"';
             cur := SYS.DBMS_SYS_SQL.open_cursor;
             SYS.DBMS_SYS_SQL.parse_as_user(
                   c => cur,
                   statement => plsql,
                   language_flag => DBMS_SQL.native,
                   userID => uid
          );
             rc := SYS.DBMS_SYS_SQL.execute(cur);

             SYS.DBMS_SYS_SQL.close_cursor(cur);
   end;
   /
 
 exec Drop_LINK( 'WGPOMSUSR', 'DBNAME' );

 select * from dba_db_links;

 drop procedure Drop_DbLink;

Featured Post

Apply Patch 22191577 latest GI PSU to RAC and DB homes using Opatch auto or manual steps

Patch 22191577: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.160119 (JAN2016) Unzip the patch 22191577 Unzip latest Opatch Version in or...