DROP DB_LINKS of a PRIVATE user from “SYS”

February 21st, 2012 | Posted by Nassyam Basha inAdministration

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 userUSER 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 separationdrop database link "CKPT"."DEVWEBSTORE10G_IC.CKPT.COM "                   *ERROR at line 1:ORA-02024: database link not foundSQL> drop database link DEVWEBSTORE10G_IC.CKPT.COM;  <---- Drop by using without schema namedrop database link DEVWEBSTORE10G_IC.CKPT.COM                   *ERROR at line 1:ORA-02024: database link not foundSQL> drop database link CKPT. DEVWEBSTORE10G_IC.CKPT.COM; <---- Drop by using without schema name using pointerdrop database link CKPT. DEVWEBSTORE10G_IC.CKPT.COM                   *ERROR at line 1:ORA-02024: database link not foundSQL>

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 selectedSQL>

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.COMCKPT                            DEVWEBSTORE9I_IC.WORLDCKPT                            INTER_EDI_RO.CKPT.COMCKPT                            ORDERSHIPPING.CKPT.COMCKPT                            ORDERSHIPPING.WORLDCKPT                            SVC_IW.CKPT.COM6 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 selectedSQL>from :http://www.oracle-ckpt.com/drop-db_links-of-a-private-user-from-sys/