Create database-link in another user’s schema

Ever wondered, why you can’t create db-links in the schema of another user – not even as DBA?

When creating objects like tables in another schema, we prefix the tablename with the name of the target-schema (e.g. “create table appuser.first_table …”). But in db-link-names we are allowed to use dots as part of the name – and hence can’t prefix like usual.

Here are three solutions to this problem:

Marko Sutic uses the “password hijacking approach”,

Neil Johnson creates a db-link-create-procedure in the target-schema

and on “Oracle DBA Tips” we find a sophisticated solution using DBMS_SYS_SQL without any need to create foreign objects or hijack passwords.

The latter I hadn’t tested yet, but it looks very interesting.

My favorite is the db-link-create-procedure. Here is a generic procedure for that matter:

CREATE PROCEDURE targetschema.create_db_link (linkname in varchar2, remote_user in varchar2, remote_pwd in varchar2, conn_string in varchar2) is
  V_SQL varchar2(1000);
    V_SQL := 'CREATE DATABASE LINK ' || linkname || ' CONNECT TO ' || remote_user || ' IDENTIFIED BY ' || remote_pwd || ' USING ''' || conn_string || '''';
END create_db_link;

grant create database link to targetschema;

exec targetschema.create_db_link('linkname','user','pwd','connectstring');

And maybe a cleanup afterwards:

revoke create database link from targetschema;

drop procedure targetschema.create_db_link;

Dropping database-links in another schema is analogously:

CREATE PROCEDURE targetschema.drop_db_link (linkname in varchar2) is
  V_SQL varchar2(100);
    V_SQL := 'DROP DATABASE LINK ' || linkname;
END drop_db_link;
select * from dba_db_links;

exec targetschema.drop_db_link('linkname');