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
Hi,
Here is another way to do that:
https://doganay.wordpress.com/2015/10/19/create-private-database-link-for-another-user-using-dbms_sys_sql-parse_as_user/
Please use Porxy user to do it, it’s the “correct” way to do it.
Proxy User:
ALTER USER GRANT CONNECT THROUGH SYSTEM;
CONN SYSTEM[DEST]
DROP DATABASE LINK
CREATE DATABASE LINK ….
as sys
delete link$ where NAME=”;
commit ;
thanks for sharing, works propertly