DROP DB_LINKS of a PRIVATE user from “SYS”

DROP DB_LINKS of a PRIVATE user from “SYS”

Create a procedure named “DROP_DBLINK” which will call values from “dba_users”, also which parse a cursor in it and it calls an inbuilt package also.
To drop a private DB_LINK either we need to change user password or we need to know user password, Instead of that we can drop DB_LINKS using this procedure.

Step 1:- Check the DB_LINK & Troubleshoot to drop

a) Check the existing DB_LINK of user “CKPT”.

SQL> show user
USER is "SYS"
SQL>

SQL> select db_link,owner from dba_db_links where owner='CKPT' and db_link= ‘DEVWEBSTORE10G_IC.CKPT.COM’;

DB_LINK                        OWNER
------------------------------ ------------------------------
DEVWEBSTORE10G_IC.CKPT.COM       CKPT

b) Drop the DB_LINK from “SYS” user.

SQL> drop database link "CKPT"."DEVWEBSTORE10G_IC.CKPT.COM "; <---- Drop by using schema name with separation
drop database link "CKPT"."DEVWEBSTORE10G_IC.CKPT.COM "
                   *
ERROR at line 1:

ORA-02024: database link not found

SQL> drop database link DEVWEBSTORE10G_IC.CKPT.COM;  <---- Drop by using without schema name
drop database link DEVWEBSTORE10G_IC.CKPT.COM
                   *
ERROR at line 1:
ORA-02024: database link not found
SQL> drop database link CKPT. DEVWEBSTORE10G_IC.CKPT.COM; <---- Drop by using without schema name using pointer
drop database link CKPT. DEVWEBSTORE10G_IC.CKPT.COM
                   *
ERROR at line 1:
ORA-02024: database link not found
SQL>

C) Create a procedure as below from “SYS” user.

SQL>  Create or replace procedure Drop_DbLink(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;
   /
Procedure created.
SQL>

D) Now drop one DB_LINK of a Private user

SQL> exec Drop_DbLink( 'CKPT', 'DEVWEBSTORE10G_IC.CKPT.COM' );
PL/SQL procedure successfully completed.
SQL>
SQL> select db_link,owner from dba_db_links where owner='CKPT' and db_link='DEVWEBSTORE10G_IC.CKPT.COM';
no rows selected
SQL>

Here No DB_LINK exists with the above name after Executing Procedure.

Step 2:- How to DROP ALL DB_LINKS of a “PRIVATE” schema from “SYS” user

This procedure is an extended for the above procedure “Drop_DbLink”, Create a procedure named “Dropschema_dblinks”

create or replace procedure DropSchema_DbLinks(schemaName varchar2 ) is
    begin
            for link in(
                    select
                            l.db_link
                    from    dba_db_links l
                    where   l.owner = schemaName
            ) loop
                    Drop_DbLink(
                           schemaName => schemaName,
                           dbLink => link.db_link
                   );
           end loop;
   end;
   /
Procedure created.
SQL>

SQL> select owner, db_link from dba_db_links where owner ='CKPT';
 OWNER                          DB_LINK
------------------------------ ------------------------------
CKPT                            DEVWEBSTORE9I_IC.CKPT.COM
CKPT                            DEVWEBSTORE9I_IC.WORLD
CKPT                            INTER_EDI_RO.CKPT.COM
CKPT                            ORDERSHIPPING.CKPT.COM
CKPT                            ORDERSHIPPING.WORLD
CKPT                            SVC_IW.CKPT.COM
6 rows selected.

SQL> exec dropschema_dblinks('CKPT');
 PL/SQL procedure successfully completed.
SQL>

SQL> select owner, db_link from dba_db_links where owner ='CKPT';
no rows selected
SQL>

Here it is all the “6” DB_LINKS dropped at one shot.  🙂