Skip to content

Create DB Links for Another User, Can We?

  • by

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

Prerequisites for Creating DB 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 in local database, whichever privilege is to be used.
  • Grant CREATE SESSION to the user in remote database.

Create a DB Link in 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

HR does not have it. Let's check SYS.

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

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

As you can see, the newly created database link falls in SYS realm, not HR, which means that adding schema name before object name does not work here. We drop it for more tests.

SQL> drop database link HR.DBLINK1;

Database link dropped.

In case that SQL parser misunderstand our intention, we can try another way to make sure the schema is specified by double-quoting all the identifiers.

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

Unluckily, it doesn't work, either. The above result shows that we cannot qualify any database link with the name of a schema. Why?

Fact

This is because periods (".", dots) are permitted in name 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 19c SQL Language Reference: CREATE DATABASE LINK.