Managing Database Links in Oracle: A Step-by-Step Guide

This guide walks you through creating, managing, and deleting database links (DBlinks) in Oracle. Database links enable connectivity between Oracle databases, allowing data sharing and querying.

1. Overview

What is a Database Link? A database link is a schema object that defines a connection path to a remote database.

Types of Database Links:

• Private Database Link: Accessible only to the schema owner.
• Public Database Link: Accessible to all users in the database.
• Global Database Link: Defined in an OID or Oracle Names Server for network-wide use.

To find the global database name, use:

SELECT * FROM global_name;

2. Environment Setup

Source Database Details

• Hostname: source-db.example.com
• DB Name: source_db
• Schema: source_user/source_password

TNS Entry:

source_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = source-db.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = source_db)
    )
  )

Target Database Details

• Hostname: target-db.example.com
• DB Name: target_db
• Schema: target_user/target_password

TNS Entry:

target_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = target-db.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = target_db)
    )
  )

3. Add TNS Entry

Add the target database TNS entry to the tnsnames.ora file on the source database:

target_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = target-db.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = target_db)
    )
  )

4. List Existing Database Links

To view existing database links:

SELECT * FROM dba_db_links;

If no links exist, you will see:

no rows selected

5. Create a Public Database Link

Public database links allow all users in the source database to access the target database.

Using TNS Entry

CREATE PUBLIC DATABASE LINK target_link
CONNECT TO target_user IDENTIFIED BY target_password
USING 'target_db';

Without Modifying TNS Entry

CREATE PUBLIC DATABASE LINK target_link_no_tns
CONNECT TO target_user IDENTIFIED BY target_password
USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = target-db.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = target_db)
    )
  )';

Using Easy Connect

CREATE PUBLIC DATABASE LINK target_link_easy
CONNECT TO target_user IDENTIFIED BY target_password
USING 'target-db.example.com:1521/target_db';

6. Create a Private Database Link

Private database links are accessible only to the schema owner.

Grant Privileges

GRANT CREATE DATABASE LINK TO source_user;

Using TNS Entry

CREATE DATABASE LINK private_link
CONNECT TO target_user IDENTIFIED BY target_password
USING 'target_db';

Without Modifying TNS Entry

CREATE DATABASE LINK private_link_no_tns
CONNECT TO target_user IDENTIFIED BY target_password
USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = target-db.example.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = target_db)
    )
  )';

Using Easy Connect

CREATE DATABASE LINK private_link_easy
CONNECT TO target_user IDENTIFIED BY target_password
USING 'target-db.example.com:1521/target_db';

7. List Database Links Again

Run the following query for detailed database link information:

SET LINES 180 PAGES 999
COLUMN owner FORMAT A15
COLUMN db_link FORMAT A15
COLUMN username FORMAT A15
COLUMN host FORMAT A39
COLUMN creation_date FORMAT A20

SELECT owner, db_link, username, host,
       TO_CHAR(created, 'MM/DD/YYYY HH24:MI:SS') AS creation_date
FROM dba_db_links;

8. Verify Database Links

Verify Public Database Links

SELECT COUNT(*) FROM sample_table@target_link;       -- Using TNS entry
SELECT COUNT(*) FROM sample_table@target_link_no_tns; -- Without TNS entry
SELECT COUNT(*) FROM sample_table@target_link_easy;   -- Easy connect

Verify Private Database Links

SELECT COUNT(*) FROM sample_table@private_link;       -- Using TNS entry
SELECT COUNT(*) FROM sample_table@private_link_no_tns; -- Without TNS entry
SELECT COUNT(*) FROM sample_table@private_link_easy;   -- Easy connect

9. Drop Public Database Links

To drop public database links, login as the owner and execute:

DROP PUBLIC DATABASE LINK target_link;
DROP PUBLIC DATABASE LINK target_link_no_tns;
DROP PUBLIC DATABASE LINK target_link_easy;

10. Drop Private Database Links

To drop private database links, login as the schema owner and execute:

DROP DATABASE LINK private_link;
DROP DATABASE LINK private_link_no_tns;
DROP DATABASE LINK private_link_easy;

To confirm deletion:

SELECT * FROM dba_db_links;

If no links exist, you will see:

no rows selected

Thank you for exploring this guide on managing Oracle database links. For further details and in-depth understanding, refer to the official Oracle documentation on database links.