Can We Create Database Links for Another Schema?

  • by

The answer is no. You can see the reason why we can't create database links for another user in this post.

Prerequisites for Creating Database Links

Before proceeding any further, you have to make sure the users involving in the database link have been granted proper system privileges:

  • Grant CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK to the user on local database, whichever privilege is to be used.
  • Grant CREATE SESSION to the user on remote database.

Trying to Create a Database Link for Another User

Let's try to create a database link for HR by SYS and see what will happen.

SQL> show user;
USER is "SYS"
SQL> create database link HR.DBLINK1 connect to hr identified by hr using 'erpdb';

Database link created.

SQL> select db_link from dba_db_links where owner = 'HR';

no rows selected

SQL> select db_link from dba_db_links where owner = 'SYS';

DB_LINK
--------------------------------------------------------------------------------
HR.DBLINK1

As you can see, the newly created database link is in SYS realm, not HR, which means that adding schema name before object name does not work here.

In case the interpreter misunderstand the syntax, we can try another way to make sure the schema is added by double-quoting all the identifiers. Unluckily, it doesn't work, either.

SQL> drop database link HR.DBLINK1;

Database link dropped.

SQL> create database link "HR"."DBLINK1" connect to hr identified by hr using 'erpdb';

Database link created.

SQL> select db_link from dba_db_links where owner = 'HR';

no rows selected

SQL> select db_link from dba_db_links where owner = 'SYS';

DB_LINK
--------------------------------------------------------------------------------
HR.DBLINK1

The above result shows that we cannot qualify any database link with the name of a schema. Why? This is because periods (".", dots) are permitted in names of database links, so Oracle Database interprets the entire name for your database links including periods.

Further reading: DB Link, How and Why

The Correct Way to Create Private Database Links

You have to create the database links in that user's session.

SQL> drop database link HR.DBLINK1;

Database link dropped.

SQL> conn hr/hr;
Connected.
SQL> create database link DBLINK1 connect to hr identified by hr using 'erpdb';

Database link created.

SQL> select db_link from all_db_links where owner = 'HR';

DB_LINK
--------------------------------------------------------------------------------
DBLINK1

Maybe you were considering to switch CURRENT_SCHEMA to target user like the following. But I can tell you that it doesn't work. (MOS Doc ID 309809.1)

SQL> alter session set current_schema = HR;

Session altered.


SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
HR

For more explanations about how to create database links, please visit Oracle 18c SQL Language Reference: CREATE DATABASE LINK.