Lesson 10	Creating a database link
Objective	Explain the purpose and syntax of Oracle database links.

Purpose of Oracle Database links using Network Topology in Oracle 23c.

In Oracle 23c, a database link (DB link) is a schema object in one database that enables you to access objects in another database. It's a foundational part of distributed database systems, where Oracle supports executing SQL operations across multiple databases transparently over the network. Using network topology, Oracle database links become especially important for building federated database environments, replication, data integration, and service-oriented architectures. Here’s a structured breakdown of its purpose, syntax, and topological integration in Oracle 23c:
Purpose of Oracle Database Links in Network Topology (Oracle 23c)

Remote Querying:
- Allows you to run queries against a remote database as if it were local (e.g., SELECT * FROM employees@remotedb;).
Data Integration Across Nodes:
- Useful in multi-region cloud deployments and hybrid on-prem/cloud models where databases need to exchange data.
Replication and Materialized Views:
- Supports fast refresh of materialized views by retrieving only changed rows over the link.
PL/SQL Remote Execution:
- Enables invoking stored procedures/functions on a remote database.
Database Consolidation:
- Centralize data access without physically moving the data.

🔧 Syntax of Database Link in Oracle 23c

CREATE [PUBLIC] DATABASE LINK link_name
  CONNECT TO remote_user IDENTIFIED BY password
  USING 'tns_service_name';

PUBLIC: If specified, all users can use the link. Otherwise, it's private to the schema.
link_name: The name you assign to the link (used in SQL as @link_name).
CONNECT TO remote_user IDENTIFIED BY password: Credentials used to connect to the remote DB.
USING 'tns_service_name': This refers to the network alias defined in tnsnames.ora or Oracle Net service configuration.

📌 Example:

CREATE DATABASE LINK hr_link
  CONNECT TO hr_user IDENTIFIED BY hr_pass
  USING 'remotehrdb';

🌐 How Database Links Fit Into Oracle 23c Network Topology

Client–Server Layering:
- A local Oracle instance acts as a client to the remote Oracle server over a Net8 connection using TCP/IP.
TNS Layer (Transparent Network Substrate):
- Uses Oracle Net stack for resolving the alias in USING '...' and connecting over TCP/IP.
Distributed SQL Layer:
- Oracle transparently handles distributed transactions, commit coordination, and data consistency when operating across DB links.
Integration with Oracle Services (like Oracle Cloud):
- Supports secure connectivity to Oracle Cloud databases using Oracle Wallet, encrypted links, and access control lists (ACLs).

🔐 Security and Enhancements in Oracle 23c

Passwordless DB Links using Oracle Wallet:

          CREATE DATABASE LINK secure_link
            CONNECT TO hr_user
            IDENTIFIED BY VALUES 'EXTERNAL PASSWORD'
            USING 'remotehrdb';

Credentials stored in the wallet instead of in plain text.

Fine-Grained Access Control:

Use Oracle Database Vault or ACLs to restrict access through DB links.

Global Naming Enforcement:

ALTER SYSTEM SET global_names = TRUE;

Ensures DB link names match target database global names.
🖼️ Network Topology Diagram Overview (Oracle 23c Example)

 
+------------+           +-------------------+           +---------------------+
|  Client    | <-------> |  Oracle 23c DB_A  | <-------> |  Oracle 23c DB_B    |
| Application|  TCP/IP   |   (Local Site)    |  DB Link  | (Remote Site/Cloud) |
+------------+           +-------------------+           +---------------------+
                                   |
                        +-----------------------+
                        | TNS: 'remotehrdb'     |
                        | Listener on DB_B side |
                        +-----------------------+

📘 Summary

Feature	Purpose
DB Link	Access remote tables, views, and PL/SQL
Syntax	`CREATE DATABASE LINK ...`
TNS Entry	Required in `tnsnames.ora` or LDAP directory
Security	Use Oracle Wallet for secure credentials
Topology Role	Connects databases across LAN/WAN/Cloud

Oracle 23c database link network topology:

**1. Client Application**

**Position**: Far left.

**Role**: Acts as the initiator of requests to the database.

**Connection**: Communicates with the local Oracle 23c database using the **TCP/IP protocol**.

**2. Oracle 23c DB_A (Local Site)**

**Position**: Center of the diagram.

**Role**: This is the **originating database** that contains the **database link**.

**Responsibilities**:

Receives SQL requests from the client.

Uses a **database link** to reach the remote database.

Resolves network addresses using the **TNS service name**.

**3. Oracle 23c DB_B (Remote Site/Cloud)**

**Position**: Far right.

**Role**: This is the **target remote database** referenced by the DB link.

**Usage**:

Houses tables, views, or PL/SQL that the local DB wants to access.

Typically runs in a **different geographical location** or cloud region.

**4. TNS: "remotehrdb" Listener on DB side**

**Position**: Below Oracle 23c DB_A.

**Function**: Represents the **TNS entry** (defined in `tnsnames.ora` or LDAP).

**Purpose**:

Resolves the connection details for `DB_B`.

Provides the **network address, port, and SID/service name** for the remote listener.

**🔄 Connections**

**TCP/IP**: Connects the Client Application to the local DB (`DB_A`).

**DB Link**: Connects `DB_A` to `DB_B` using the Oracle Net layer.

**TNS Resolution**: Ensures `DB_A` knows how to contact `DB_B` through the listener.

This topology illustrates *secure, distributed data access* in an Oracle 23c ecosystem, commonly used in multi-tier architectures, *cloud replication*, and *federated querying*.

After TNS connectivity has been established, database links are used to establish SQL-level connectivity between distributed databases. The first step is to create a hierarchical model to define the topology of your system. For our class project, the model might look like the following:

Database links are essentially transparent to the users of an Oracle distributed database system because the name of a database link is the same as the global name of the database to which the link points. For example, the following SQL statement creates a database link in the local database that describes a path to the remote Raleigh database.

CREATE PUBLIC DATABASE LINK 
raleigh
connect to order_schema_user using secret_password
using ‘RAL’

Purpose of Database link

Remember, the only purpose of a database link is to add a USER_ID and password to the TNS service name so the database will connect transparently. In the statement above, the TNS service name is RAL, and an entry called RAL must exist in the tnsnames.ora file. After creating a database link, applications connected to the local database can access data from the remote Raleigh database. The USER_ID that is specified in a database link must have the proper authority to perform your requested operations. For example, if you specify a user called CUST_OWNER in the database link, you will only have authority to perform SQL on the tables that are owned by CUST_OWNER, or those tables that have been granted to CUST_OWNER.
In the next lesson, we will look at how to establish connections over database links.