Introduction

In real-world enterprise systems, data is rarely stored in a single database.
Applications often need to read or write data across multiple Oracle databases.

This is where Database Links (DB Links) come into play.

A Database Link allows one Oracle database to access objects (tables, views, procedures) in another Oracle database.

Think of it as a secure bridge between two databases.

What Is a Database Link (Simple Explanation)

A DB Link is a logical connection stored in one Oracle database that allows it to connect to another Oracle database.

Example in simple terms:

“From Database A, I want to run a SELECT on a table that physically exists in Database B.”

Without DB Link → ❌ Not possible
With DB Link → ✅ Possible

Why DB Links Are Used in Production

Common real-world use cases:

Reporting databases accessing production data
Data migration between databases
Distributed applications
Cross-system reconciliation
Legacy system integration

How DB Links Work (Behind the Scenes)

User runs a query with @dblink
Local database connects to remote database
Remote database executes the SQL
Results are returned to local database

The remote database does not know it is being accessed remotely — it behaves like a normal session.

Types of Database Links in Oracle

1. Private Database Link

Created by a user
Accessible only by that user

2. Public Database Link

Created by DBA
Accessible by all users

3. Fixed User DB Link

Always connects as the same remote user

4. Connected User DB Link

Uses the same username on both databases

Syntax to Create a Database Link

CREATE DATABASE LINK dblink_name
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'tns_service_name';

Example: Creating a DB Link

CREATE DATABASE LINK hr_dblink
CONNECT TO hr IDENTIFIED BY hr
USING 'ORCL_REMOTE';

Now hr_dblink connects to the HR schema in the remote database.

Using a DB Link – Simple Example

SELECT * FROM employees@hr_dblink;

This query:

Runs from local DB
Fetches data from remote DB
Displays results locally

Insert / Update Using DB Link

Insert Data into Remote Database

INSERT INTO employees@hr_dblink
VALUES (300, 'JOHN', 'CLERK', 7902, SYSDATE, 3000, NULL, 20);

COMMIT;

Update Remote Table

UPDATE employees@hr_dblink
SET salary = salary + 500
WHERE employee_id = 300;

COMMIT;

Production Considerations (Very Important)

✔ Network latency affects performance
✔ Large result sets can slow queries
✔ Transactions are distributed
✔ Locks can span databases

Never treat DB links like local tables in high-volume OLTP queries

Two-Phase Commit (Distributed Transactions)

When a transaction spans multiple databases:

Oracle prepares both databases
Commit happens only if both succeed
Ensures data consistency

This is called Two-Phase Commit (2PC).

Important Views to Monitor DB Links

Check Existing DB Links

SELECT * FROM user_db_links;

DBA view:

SELECT * FROM dba_db_links;

Dropping a DB Link

DROP DATABASE LINK hr_dblink;

Public link:

DROP PUBLIC DATABASE LINK hr_dblink;

Security Best Practices

✔ Avoid public DB links unless required
✔ Use minimum privileges on remote user
✔ Rotate passwords periodically
✔ Consider using Oracle Wallet instead of plain passwords

Hands-On Lab: Practice DB Links Step by Step

Lab Setup Assumption

Local DB: ORCL_LOCAL
Remote DB: ORCL_REMOTE
Both have HR schema

Lab Step 1: Verify TNS Connectivity

tnsping ORCL_REMOTE

Lab Step 2: Create DB Link

CREATE DATABASE LINK hr_lab_link
CONNECT TO hr IDENTIFIED BY hr
USING 'ORCL_REMOTE';

Lab Step 3: Test the Connection

SELECT sysdate FROM dual@hr_lab_link;

If successful → DB link works ✅

Lab Step 4: Query Remote Table

SELECT employee_id, first_name
FROM employees@hr_lab_link
WHERE department_id = 10;

Lab Step 5: Join Local and Remote Tables

SELECT l.emp_id, r.first_name
FROM local_emp l
JOIN employees@hr_lab_link r
ON l.emp_id = r.employee_id;

Lab Step 6: Clean Up

DROP DATABASE LINK hr_lab_link;

Practice Exercises

Exercise 1

Create a private DB link to a remote schema and fetch data from DUAL.

Exercise 2

Insert 5 rows into a remote table using DB link.

Exercise 3

Simulate a distributed transaction and perform rollback.

Exercise 4

List all DB links created by your user.

Quiz: Test Your Understanding

Questions

Q1. What is a database link used for?
A. Backup
B. Cross-database access
C. Index creation
D. Performance tuning

Q2. Which symbol is used to access remote objects?
A. #
B. $
C. @
D. %

Q3. Who can create a public DB link?
A. Any user
B. SYS only
C. DBA
D. HR

Q4. Which view shows DB links created by the current user?
A. DBA_DB_LINKS
B. USER_DB_LINKS
C. ALL_TABLES
D. V$DATABASE

Q5. What major risk exists with DB links?
A. Data corruption
B. Password exposure
C. Network dependency
D. All of the above

Answers

Final Best Practices Summary

✔ Use DB links only when required
✔ Avoid heavy joins over DB links
✔ Monitor long-running distributed queries
✔ Secure credentials properly
✔ Drop unused DB links

Conclusion

Database Links are powerful but dangerous if misused.

Used wisely → seamless integration
Used carelessly → performance & security risks

Examples of Each Type of Database Link in Oracle

To make DB Links easy to understand, assume:

Local Database: ORCL_LOCAL
Remote Database: ORCL_REMOTE
TNS service name of remote DB: ORCL_REM
Remote schema users: HR, SALES

1️⃣ Private Database Link (Most Common)

What It Is

Created by a normal user
Only that user can use it
Very common in applications

Example: Private DB Link

Logged in as user APPUSER on local database:

CREATE DATABASE LINK hr_private_link
CONNECT TO hr IDENTIFIED BY hr
USING 'ORCL_REM';

Usage

SELECT * FROM employees@hr_private_link;

✔ Only APPUSER can use this DB link
❌ Other users cannot see or use it

When to Use

Application-specific access
Reporting by a single schema
Better security than public DB links

2️⃣ Public Database Link

What It Is

Created by DBA
Accessible to all users
Must be used carefully

Example: Public DB Link

Connected as DBA:

CREATE PUBLIC DATABASE LINK hr_public_link
CONNECT TO hr IDENTIFIED BY hr
USING 'ORCL_REM';

Usage (Any User)

SELECT * FROM employees@hr_public_link;

✔ All users can access
❌ Higher security risk

When to Use

Shared reference data
Common lookup tables
When many schemas need access

3️⃣ Fixed User Database Link

What It Is

Always connects to the same remote user
Username & password are stored in DB link
Most DB links are fixed user DB links

Example: Fixed User DB Link

CREATE DATABASE LINK sales_fixed_link
CONNECT TO sales IDENTIFIED BY sales_pwd
USING 'ORCL_REM';

Behavior

SELECT USER FROM dual@sales_fixed_link;

📌 Output will always be:

SALES

No matter who runs the query locally, the remote user is always SALES.

When to Use

Controlled access
Read-only reporting users
Stable security model

4️⃣ Connected User Database Link

What It Is

Uses same username on local and remote database
No username/password stored in DB link
Requires identical users on both databases

Example Setup

Assume:

User HR exists on both databases
Same password on both databases

Create Connected User DB Link

CREATE DATABASE LINK hr_connected_link
USING 'ORCL_REM';

(No CONNECT TO clause)

Usage

SELECT USER FROM dual@hr_connected_link;

📌 Output:

HR

Important Rule

Local User	Remote User
HR	HR
SCOTT	❌ fails unless SCOTT exists remotely

When to Use

Trusted environments
Same user management across databases
Simplified credential handling

🔁 Comparison Table (Quick Revision)

Type	Who Creates	Who Can Use	Remote User
Private DB Link	Normal User	Only creator	Fixed
Public DB Link	DBA	All users	Fixed
Fixed User DB Link	User/DBA	Depends	Same remote user always
Connected User DB Link	User/DBA	Same user only	Same username

Real-World Production Recommendation

✔ Prefer Private + Fixed User DB Links
✔ Avoid Public DB Links unless required
✔ Use Connected User DB Links only in trusted setups
✔ Always restrict remote user privileges

Disclaimer:
The examples and commands provided in this article are for educational purposes only. Database Links involve cross-database connectivity and security considerations. Always validate configurations in a non-production environment and follow your organization’s security and change-management policies before implementing them in production systems.