Unfortunately in Oracle you can’t create or drop database link in another schema. It’s very frustrating. Simple solution is to create anonymous block which will first create dummy function in another schema and then reuse it to create or drop an object(in our case database link).

Of course it can be reused to do other types of object as well.

Let’s prepare some sample data. As user SYS

SQL> CONNECT / AS SYSDBA
Connected.

GRANT CONNECT TO tomasz IDENTIFIED BY test1234;

Grant succeeded.

GRANT CREATE DATABASE LINK TO tomasz;

Grant succeeded.

Now let’s create database link as user TOMASZ

SQL> CONNECT tomasz/test1234
Connected.

SQL> CREATE DATABASE LINK ORA11G 
     CONNECT TO tomasz IDENTIFIED BY test1234 
     USING 'ORA11G';

Database link created.

Let’s try drop database link created in schema TOMASZ using SYS account

SQL> CONNECT / as sysdba
Connected.

SQL> DROP DATABASE LINK tomasz.ora11g;
DROP DATABASE LINK tomasz.ora11g
                   *
ERROR at line 1:
ORA-02024: database link not found

Solution is following anonyms simple pl/sql block. It will drop all database link in schema TOMASZ.

SQL> CONNECT / as sysdba
Connected.

set serveroutput on
DECLARE
  l_sql CLOB :=
   'CREATE PROCEDURE <OWNER>.drop_db_links_prc
    IS
    BEGIN
      FOR i IN (SELECT * FROM user_db_links)
      LOOP
        dbms_output.put_line(''to drop ''||i.db_link);
        EXECUTE IMMEDIATE ''DROP DATABASE LINK ''||i.db_link;
      END LOOP;
    END;';
  l_sql1 clob;  
BEGIN
  FOR i in (SELECT DISTINCT owner 
              FROM dba_objects
             WHERE owner IN ('TOMASZ')
               AND object_type='DATABASE LINK')
  LOOP
    l_sql1 := REPLACE(l_sql, '<OWNER>', i.owner);
    dbms_output.put_line(l_sql1);
    EXECUTE IMMEDIATE l_sql1;
    
    l_sql1 := 'BEGIN '||i.owner||'.drop_db_links_prc; END;';
    
    EXECUTE IMMEDIATE l_sql1;
    
    l_sql1 := 'DROP PROCEDURE '||i.owner||'.drop_db_links_prc';

    EXECUTE IMMEDIATE l_sql1;
  END loop;
END;
/

to drop ORA11G.DBAORA.COM

PL/SQL procedure successfully completed.

Creation of database link in another schema will not work :). On the begging you can be surprised that command succeed but owner  of the database link is SYS and name is different than you can expect.

SQL> CONNECT / as sysdba
Connected.

SQL> CREATE DATABASE LINK tomasz.ORA11G
  2       CONNECT TO tomasz IDENTIFIED BY test1234
  3       USING 'ORA11G';

Database link created.

SELECT * FROM user_db_links;

DB_LINK       USERNAME  PASSWORD   HOST           CREATED
------------- -------- --------- ------ -------------------
TOMASZ.ORA11G   TOMASZ    ORA11G        25.04.2017 14:31:47

That’s because database link name consists of two parts

• database link name – specified by user
• global_name – specified by user or automatically assigned from service_name

there is no place to specify schema_name

Just modify ANONYMOUS code to create the database link :). I think you can do it yourself :o).

Have a fun:)
Tomasz