How to Create a Database Link in Oracle

How to Create a Database Link in Oracle

A database link (DB link) lets you query and manipulate objects in a remote Oracle database as if they were local. The link definition is stored in the local database and contains the connection credentials and service name for the remote database. This guide covers all DB link types with testing and troubleshooting.

Prerequisites

• Private DB link: CREATE DATABASE LINK system privilege
• Public DB link: CREATE PUBLIC DATABASE LINK system privilege
• The remote database must be reachable (tnsnames.ora entry, LDAP, or Easy Connect string)
• The remote user account must exist and have CREATE SESSION

-- Check your DB link privileges
SELECT privilege FROM session_privs
WHERE privilege LIKE '%DATABASE LINK%';

-- Check existing database links
SELECT owner, db_link, username, host, created
FROM dba_db_links
ORDER BY owner, db_link;

Step 1: Create a Private Database Link (Fixed User)

A private link is owned by the creating user and uses a fixed remote username and password.

-- Private link using tnsnames.ora entry
CREATE DATABASE LINK remote_prod
  CONNECT TO app_schema IDENTIFIED BY "RemoteP@ss2024"
  USING 'PROD_DB';

-- Using Easy Connect (no tnsnames.ora needed)
CREATE DATABASE LINK remote_prod_ez
  CONNECT TO app_schema IDENTIFIED BY "RemoteP@ss2024"
  USING '192.168.1.100:1521/PROD';

-- Using full connection descriptor inline
CREATE DATABASE LINK remote_prod_full
  CONNECT TO app_schema IDENTIFIED BY "RemoteP@ss2024"
  USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-server)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PROD)))';

Step 2: Create a Public Database Link

A public link is accessible to all users in the local database. Any user can query remote objects through it.

-- Create a public database link (requires CREATE PUBLIC DATABASE LINK privilege)
CREATE PUBLIC DATABASE LINK pub_link_prod
  CONNECT TO reporting_user IDENTIFIED BY "RepPass2024"
  USING 'PROD_DB';

-- Public link with Easy Connect
CREATE PUBLIC DATABASE LINK pub_link_dw
  CONNECT TO dw_reader IDENTIFIED BY "DwPass2024"
  USING 'dw-server:1521/DW';

Step 3: Create a Connected User Link

A connected user link uses the credentials of the local session — no embedded password. The local user must have a matching account on the remote database.

-- Connected user link (no CONNECT TO clause)
CREATE DATABASE LINK remote_via_current_user
  USING 'PROD_DB';

-- When a user named APP_SCHEMA runs a query through this link,
-- Oracle connects to PROD_DB as APP_SCHEMA using their current authentication

Step 4: Test the Database Link

-- Test connectivity to the remote database
SELECT * FROM dual@remote_prod;
-- Should return: X

-- Query a remote table
SELECT COUNT(*) FROM app_schema.orders@remote_prod;

-- Query with column alias to distinguish local vs remote
SELECT o.order_id, o.order_date, c.customer_name
FROM orders@remote_prod o
JOIN customers@remote_prod c ON o.customer_id = c.customer_id
WHERE o.status = 'PENDING'
FETCH FIRST 10 ROWS ONLY;

-- Get remote database name to confirm which database you're connected to
SELECT name, db_unique_name, open_mode
FROM v$database@remote_prod;

Step 5: Create a Synonym for a Remote Object

Synonyms hide the DB link name from application code — if the link or schema changes, only the synonym needs updating.

-- Create a synonym for a remote table
CREATE SYNONYM remote_orders
  FOR app_schema.orders@remote_prod;

-- Now query transparently without specifying the link
SELECT COUNT(*) FROM remote_orders;
SELECT COUNT(*) FROM remote_orders WHERE status = 'PENDING';

-- Public synonym accessible to all users
CREATE PUBLIC SYNONYM pub_remote_orders
  FOR app_schema.orders@pub_link_prod;

Advanced Examples

Insert/Update Across a DB Link

-- Insert into a remote table
INSERT INTO app_schema.orders@remote_prod
  (order_id, customer_id, order_date, status)
VALUES (seq_order_id.NEXTVAL@remote_prod, 12345, SYSDATE, 'PENDING');
COMMIT;

-- Update rows in a remote table
UPDATE app_schema.orders@remote_prod
SET status = 'SHIPPED'
WHERE order_id = 99999;
COMMIT;

-- Distributed transaction (local + remote in one commit)
INSERT INTO local_orders (order_id, order_date)
  VALUES (12345, SYSDATE);
INSERT INTO app_schema.orders@remote_prod (order_id, order_date)
  VALUES (12345, SYSDATE);
COMMIT;  -- Two-phase commit coordinates both databases

Create a Loopback Link (Same Database)

Loopback links are useful for testing and for Data Pump network mode imports.

-- Loopback link (connects back to the same database)
CREATE DATABASE LINK loopback
  CONNECT TO system IDENTIFIED BY manager
  USING 'ORCL';

-- Test it
SELECT name FROM v$database@loopback;

Drop a Database Link

-- Drop a private database link
DROP DATABASE LINK remote_prod;

-- Drop a public database link (requires DROP PUBLIC DATABASE LINK privilege)
DROP PUBLIC DATABASE LINK pub_link_prod;

Common Mistakes and Pitfalls

ORA-12154: TNS could not resolve service name — The USING clause service name must be resolvable from the database server, not from the client machine. The tnsnames.ora file that matters is the one on the server running Oracle.

Embedded passwords in DB links — Passwords in DB links are stored in encrypted form in the data dictionary, but they expire when the remote password changes. Document all DB links so you know which ones to update when passwords rotate.

Using SYS over a DB link — Oracle does not allow connecting as SYS through a database link. Use a dedicated remote service account.

Distributed transactions across time zones — Two-phase commit (2PC) requires coordinated time between the databases. Significant clock skew causes ORA-02050 errors.

Querying DBA_ views over DB links — SELECT * FROM dba_tables@remote works but can be very slow because it reads the remote data dictionary. Cache results locally if you run these queries frequently.

Not using synonyms — Hard-coding @db_link_name in application code makes migrations painful. Always wrap remote object access in local synonyms.

Troubleshooting

-- ORA-02085: database link connects to a different database
-- Check the remote DB name matches what the link expects
SELECT db_link, username, host FROM user_db_links;
SELECT name FROM v$database@your_link_name;

-- ORA-01017: invalid username/password on remote
-- The password in the link definition may be wrong or expired
-- Drop and recreate the link with the correct password
DROP DATABASE LINK remote_prod;
CREATE DATABASE LINK remote_prod
  CONNECT TO app_schema IDENTIFIED BY "NewPassword2024"
  USING 'PROD_DB';

-- Test network connectivity from the database server OS:
-- tnsping PROD_DB
-- sqlplus app_schema/"NewPassword2024"@PROD_DB

Verification Queries

-- List all database links visible to current user
SELECT db_link, username, host, created
FROM user_db_links
ORDER BY db_link;

-- List all links in the database (DBA view)
SELECT owner, db_link, username, host, created
FROM dba_db_links
ORDER BY owner, db_link;

-- Test a specific link and confirm remote database identity
SELECT 'Link is working. Remote DB: ' || name AS test_result
FROM v$database@remote_prod;

-- Check for open DB link sessions
SELECT s.sid, s.serial#, s.username, s.program,
       l.db_link
FROM v$session s
JOIN v$dblink l ON s.saddr = l.saddr
WHERE l.db_link IS NOT NULL;

Related Topics

• How to Import Schema with Data Pump - Network mode uses DB links
• Oracle Database Links Guide - In-depth reference
• Oracle Errors: ORA-02063 - Database link errors
• Oracle Errors: ORA-12154 - TNS resolution failures