Creating Database Links in an Oracle schema with a non-owner account

Specifying CREATE DATABASE LINK statements in the SQL_DIRECT folder for a Single-Schema project creates the database link in the Oracle schema. However, this does not work in a Multi-Schema project because the SQL statements are executed as the DATICAL_USER and Oracle does not allow creating database links in another schema.

As the CREATE DATABASE LINK statements for a Multi-Schema project are currently not supported, you can use a workaround where the procedure is run as the OWNER so it creates the DBLINK in the managed schema.

Prerequisites

You can create a database link in anotheruser's schema only if anotheruser has the CREATE DATABASE LINK privilege and the user you are connected as has the CREATE ANY PROCEDURE privilege. For information on granting privileges, see Oracle documentation.

How to create database links

To create a database link in an Oracle schema with a non-owner account, follow the example:

create procedure anotheruser."tmp_doit_200906121431"
is
begin
execute immediate '
create database link remote_db_link
connect to remote_user
identified by remote_password
using ''remote_db'' ';
end;
/
begin
anotheruser."tmp_doit_200906121431";
end;
/
drop procedure anotheruser."tmp_doit_200906121431"
/

Create a procedure in the anotherusers's schema that contains the CREATE DATABASE LINK statement. When the procedure is being executed, it runs as the owner so that the CREATE DATABASE LINK statement is executed by anotheruser.

The name of the procedure is not important, however, make sure that it doesn't have conflicts with any existing object names. You can run a query of DBA_OBJECTS to check that a matching object_name does not exist.