How to Create/Drop an Oracle Database Link from Another Schema ?

To create or drop Database link from another schema in Oracle, you must use the Stored Procedures.



SQL> create or replace procedure BIB_CTL.CREATE_DB_LINK AS
  2  BEGIN
  3  EXECUTE IMMEDIATE 'create database link BIB_CLF_LINK connect to MyUSERNAME       identified by MyPASSWORD using "CLF"';
  4  end;
  5  /

Procedure created.

SQL> exec BIB_CTL.CREATE_DB_LINK;

PL/SQL procedure successfully completed.



SQL> select * from dba_db_links where OWNER='BIB_CTL';

OWNER       DB_LINK        USERNAME         HOST                         CREATED
--------------   ----------------   --------------------     ------------------------------ -------------------------
BIB_CTL    BIB_CLF        MyUSERNAME     CLF                               03-APR-17

SQL> drop database link BIB_CTL.BIB_CLF_LINK;
drop database link BIB_CTL.BIB_CLF_LINK
*
ERROR at line 1:
ORA-02024: database link not found


SQL> CREATE PROCEDURE BIB_CTL.DROP_DB_LINK AS
BEGIN
EXECUTE IMMEDIATE 'drop database link BIB_CLF_LINK';
END;
5 /

Procedure created.

SQL> exec BIB_CTL.DROP_DB_LINK

PL/SQL procedure successfully completed.

SQL> select * from dba_db_links where OWNER='BIB_CTL';

no rows selected

SQL>