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
SRCDB · srchost
owns the DB link
TGTDB · tgthost
listener + remote user
| Source | Target | |
|---|---|---|
| Database | SRCDB | TGTDB (service name tgtdb) |
| Host | srchost (192.168.56.101) | tgthost (192.168.56.102) |
| Role | Creates and uses the link | Accepts the connection |
| Needs | tnsnames.ora entry, CREATE DATABASE LINK privilege | Running listener, a user to connect as |
tnsnames.ora.Types of database link
| Type | Syntax | Who can use it |
|---|---|---|
| Private (default) | CREATE DATABASE LINK | Only the schema that created it |
| Public | CREATE PUBLIC DATABASE LINK | Every user in the source database |
| Fixed user | CONNECT TO user IDENTIFIED BY pwd | Always logs in to the target as that user |
| Connected user | no CONNECT TO clause | Logs 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.
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:
SQL> ALTER SYSTEM REGISTER;
Also check that the firewall allows port 1521 from the source host:
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:
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;
3Add a TNS entry on the source
Edit $ORACLE_HOME/network/admin/tnsnames.ora on the source server:
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
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 ...
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).
-- 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:
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
-- 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
-- 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
| Error | What it means | Fix |
|---|---|---|
| ORA-12154 TNS: could not resolve the connect identifier | The alias in USING is not in the source tnsnames.ora | Check the spelling and TNS_ADMIN, or use a full connect descriptor |
| ORA-12541 TNS: no listener | Nothing is listening on that host and port | Start the target listener; check host, port and firewall |
| ORA-12514 listener does not know of service | Wrong SERVICE_NAME, or the DB has not registered | Compare with lsnrctl status; run ALTER SYSTEM REGISTER |
| ORA-01017 invalid username/password | Wrong credentials stored in the link, or the remote password changed | Drop 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 name | Name the link after the remote global name, or set GLOBAL_NAMES to FALSE |
| ORA-02019 connection description not found | The link name used in the query does not exist for this user | Check dba_db_links; a private link is only visible to its owner |
| ORA-02020 too many database links in use | Session 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
