Creating a Database Link in Oracle




A database link lets one Oracle database query, and even change, tables in another database as if they were local. It is the quickest way to copy reference data, compare two environments or pull data for a one-off migration, with no export files and no extra tools.

In this post I set up a link between two Oracle 19c databases on different servers. Along the way I cover the part most guides skip: which steps run on which server, how to keep the link secure, and how to fix the errors you are most likely to hit.

The setup

SOURCE
SRCDB · srchost
owns the DB link
── Oracle Net (TCP 1521) ──▶
TARGET
TGTDB · tgthost
listener + remote user
SourceTarget
DatabaseSRCDBTGTDB (service name tgtdb)
Hostsrchost (192.168.56.101)tgthost (192.168.56.102)
RoleCreates and uses the linkAccepts the connection
Needstnsnames.ora entry, CREATE DATABASE LINK privilegeRunning listener, a user to connect as
Key idea: the link is an outgoing connection, so only the target needs a listener for it. The source just needs to know how to reach the target, through tnsnames.ora.

Types of database link

TypeSyntaxWho can use it
Private (default)CREATE DATABASE LINKOnly the schema that created it
PublicCREATE PUBLIC DATABASE LINKEvery user in the source database
Fixed userCONNECT TO user IDENTIFIED BY pwdAlways logs in to the target as that user
Connected userno CONNECT TO clauseLogs in to the target with the caller's own user name and password

For most work, a private, fixed-user link is the right choice, and it is what this guide builds.

1Prepare the target: listener and service

Make sure the target listener is running and knows the database service.

RUN ON TARGET
lsnrctl status

Expected output:

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tgthost)(PORT=1521)))
Services Summary...
Service "tgtdb" has 1 instance(s).
  Instance "tgtdb", status READY, has 1 handler(s) for this service...

Status READY means the database registered itself with the listener automatically. That is all you need; no SID_LIST_LISTENER entry is required. If the service is missing, register it from SQL*Plus:

RUN ON TARGET
SQL> ALTER SYSTEM REGISTER;

Also check that the firewall allows port 1521 from the source host:

RUN ON TARGET
sudo firewall-cmd --list-ports
sudo firewall-cmd --permanent --add-port=1521/tcp && sudo firewall-cmd --reload

2Create a dedicated user on the target

Do not point the link at a powerful account. Create a user that has only what the link needs:

RUN ON TARGET
SQL> CREATE USER link_reader IDENTIFIED BY "Str0ng#Passw0rd";
SQL> GRANT CREATE SESSION TO link_reader;
SQL> GRANT SELECT ON hr.employees   TO link_reader;
SQL> GRANT SELECT ON hr.departments TO link_reader;
Least privilege: the link password is stored in the source database. Anyone who can use the link gets exactly this user's rights on the target, so keep them to the minimum.

3Add a TNS entry on the source

Edit $ORACLE_HOME/network/admin/tnsnames.ora on the source server:

RUN ON SOURCE
TGTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tgtdb)
    )
  )

4Test the network path from the source

RUN ON SOURCE
tnsping TGTDB
nc -zv 192.168.56.102 1521
sqlplus link_reader@TGTDB

Expected output:

OK (10 msec)
Connection to 192.168.56.102 1521 port [tcp/ncube-lm] succeeded!
Connected to: Oracle Database 19c Enterprise Edition ...
Golden rule: if sqlplus user@TGTDB does not work from the source server, the database link will not work either. Fix the network first.

5Grant the privilege and create the link

Connect as the schema that will own the link (here app_owner).

RUN ON SOURCE
-- As a DBA on the source
SQL> GRANT CREATE DATABASE LINK TO app_owner;

-- As app_owner on the source
SQL> CREATE DATABASE LINK tgtdb_link
       CONNECT TO link_reader IDENTIFIED BY "Str0ng#Passw0rd"
       USING 'TGTDB';

Expected output:

Database link created.

USING 'TGTDB' refers to the TNS alias from Step 3. If you prefer not to depend on tnsnames.ora, you can put the full connect descriptor in the USING clause instead:

RUN ON SOURCE
SQL> CREATE DATABASE LINK tgtdb_link
       CONNECT TO link_reader IDENTIFIED BY "Str0ng#Passw0rd"
       USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.102)(PORT=1521))
               (CONNECT_DATA=(SERVICE_NAME=tgtdb)))';

6Test and use the link

RUN ON SOURCE
-- Quick connectivity test
SQL> SELECT * FROM dual@tgtdb_link;

-- Which database did I reach?
SQL> SELECT name, open_mode FROM v$database@tgtdb_link;

-- Query a remote table
SQL> SELECT department_id, COUNT(*)
     FROM   hr.employees@tgtdb_link
     GROUP  BY department_id;

-- Hide the link behind a synonym so applications do not need to know
SQL> CREATE SYNONYM remote_emp FOR hr.employees@tgtdb_link;
SQL> SELECT COUNT(*) FROM remote_emp;

You can also copy data across in one statement, for example INSERT INTO local_emp SELECT * FROM hr.employees@tgtdb_link; (with an INSERT grant this works in the other direction too).

7Manage existing links

RUN ON SOURCE
-- All links in the database
SQL> COL owner FOR a15
SQL> COL db_link FOR a25
SQL> COL host FOR a30
SQL> SELECT owner, db_link, username, host, created FROM dba_db_links;

-- Close an idle link session (frees the remote session)
SQL> COMMIT;
SQL> ALTER SESSION CLOSE DATABASE LINK tgtdb_link;

-- Drop a link
SQL> DROP DATABASE LINK tgtdb_link;
SQL> DROP PUBLIC DATABASE LINK some_public_link;   -- public links

dba_db_links also has a VALID column in 19c. Check it after you change the remote password.

Troubleshooting common errors

ErrorWhat it meansFix
ORA-12154 TNS: could not resolve the connect identifierThe alias in USING is not in the source tnsnames.oraCheck the spelling and TNS_ADMIN, or use a full connect descriptor
ORA-12541 TNS: no listenerNothing is listening on that host and portStart the target listener; check host, port and firewall
ORA-12514 listener does not know of serviceWrong SERVICE_NAME, or the DB has not registeredCompare with lsnrctl status; run ALTER SYSTEM REGISTER
ORA-01017 invalid username/passwordWrong credentials stored in the link, or the remote password changedDrop and recreate the link (passwords are case-sensitive)
ORA-02085 database link connects to …GLOBAL_NAMES = TRUE and the link name does not match the remote global nameName the link after the remote global name, or set GLOBAL_NAMES to FALSE
ORA-02019 connection description not foundThe link name used in the query does not exist for this userCheck dba_db_links; a private link is only visible to its owner
ORA-02020 too many database links in useSession reached the open_links limit (default 4)Close links you no longer need, or raise open_links

Best practices

  • Prefer private links. A public link is usable by every user in the database. Create one only when you really need to.
  • Use a read-only remote user unless the link must write, and grant only the objects it needs.
  • Restrict network access to the target port from the source host only.
  • Mind performance. Joining a local and a remote table can pull the whole remote table across the network. Filter on the remote side, or use the /*+ DRIVING_SITE(alias) */ hint so the join runs where the big table is.
  • Commit or roll back after using a link, even for SELECTs. The remote session stays open as part of a distributed transaction until you do.
  • Audit links regularly. Old links that point to decommissioned databases are a security and support risk.

Conclusion

A database link takes five minutes to create once the network path is right. Keep this order in mind: target listener → remote user → source TNS entry → test with SQL*Plus → create link. Most link problems are actually network problems, and you can find them before the link even exists.

Do you use database links in production, or avoid them? Tell me why in the comments.

Tamilselvan Perumal · Oracle ACE Apprentice · Lead Product Engineer & Senior DBA