Create an Oracle Database Link: Purpose and Syntax in Oracle 26ai

Understand the Two Connections

Call the originating database DB_A and the destination database DB_B. There are two distinct relationships:

1. Application to DB_A: the application authenticates to its normal database service and submits SQL.
2. DB_A to DB_B: when the statement uses a database link, DB_A acts as a client of DB_B and connects using the link's remote identity and connection information.

The link belongs at the originating database. Creating a link in a schema on DB_A does not create a reverse link on DB_B. If Raleigh must independently query headquarters, that is a separate access requirement, with its own authorization and configuration.

This distinction also explains a common troubleshooting surprise: an alias can work from a developer's laptop yet fail when used by a database link. The laptop and the database server are different Oracle Net clients. Their naming configuration, environment, firewall access, and driver behavior may differ. Test the path used by the originating database, not just the path used by your workstation.

Keep the Names and Identities Separate

A working example is easier to understand when each name has one job. The following names are intentionally different:

• raleigh_link: the link name used in local SQL after the @ character.
• order_reader: the remote login account used by a fixed-user link.
• RAL: a local naming alias used to locate the remote service in this example.
• raleigh_orders.example.com: the database service requested by the connect descriptor.
• orders_owner.order_summary: a remote schema and view that the account is permitted to read.

A naming alias is not a user account, and a listener is not a naming alias. A link name is also not necessarily the same string as a database service name. Document these distinctions in deployment notes so that someone investigating a failure can identify which value to check.

When local naming is used, the alias maps to a descriptor in tnsnames.ora. Other supported naming methods and connect strings can also supply connection information. Therefore, a database link does not universally require a particular entry in a particular workstation's tnsnames.ora file.

Prerequisites for the Example

Before creating the link, confirm the following with the database administrator:

• The local schema is allowed to create a private database link.
• The remote account can establish a session and has the required privileges on the intended remote objects.
• The originating database can resolve and reach the destination service.
• The selected link name complies with the local global-naming policy.
• The authentication and transport-security configuration is compatible at both ends.

These checks describe separate responsibilities. Being allowed to create a link locally does not authorize access to every remote table. Similarly, knowing the remote password does not establish a network route. A clear separation makes it possible to request the minimum necessary change instead of granting broad privileges whenever a test fails.

For multitenant deployments, also confirm the intended local container and remote PDB service. A connection to the wrong service can succeed while exposing a different schema context than the application expects. Record the actual service used by the example rather than relying on an informal server nickname.

Basic CREATE DATABASE LINK Syntax

CREATE DATABASE LINK link_name
  CONNECT TO remote_user IDENTIFIED BY "REPLACE_WITH_REMOTE_PASSWORD"
  USING 'remote_connect_identifier';

CREATE DATABASE LINK raleigh_link
  CONNECT TO order_reader IDENTIFIED BY "REPLACE_WITH_REMOTE_PASSWORD"
  USING 'RAL';

Resolve RAL from the Originating Database

RAL =
  (DESCRIPTION =
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = raleigh-db.example.com)
      (PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = raleigh_orders.example.com)
    )
  )

Query Through the Link

Start with a small connection test in the authorized environment:

SELECT 1 AS connection_test
FROM dual@raleigh_link;

An expected value of 1 demonstrates that this statement completed through the link. It does not prove that the remote account can read business data or that the application meets its performance requirements. Follow it with a narrowly scoped query against the actual object:

SELECT order_id, order_date, order_total
FROM orders_owner.order_summary@raleigh_link
WHERE order_date >= DATE '2026-10-01'
  AND order_date < DATE '2026-11-01';

This example assumes the view and columns exist and that order_reader has appropriate access. The half-open date range includes October values without assuming that an Oracle date contains only a calendar day. Selecting named columns makes the intended interface clearer than selecting every column.

The remote schema qualifier identifies the owner of the view. It need not be the same as the account used to connect. A read-only integration account can access a view owned by another schema when the required privileges have been granted. Prefer a deliberately designed interface over granting access to unrelated tables.

Private and Public Links Are Visibility Choices

Private and public describe how a link is made available in the local database. Fixed-user, connected-user, and current-user describe authentication behavior. Treat these as separate design decisions rather than as interchangeable link types.

A private link is the simpler starting point for a single application schema. A public link is available to all local users and requires the corresponding public-link creation privilege. It should be introduced only when that broader availability is intentional. A public link does not remove remote authorization checks, but a public fixed-user link can expose the capabilities of its configured remote identity to a broad local audience.

For example, if the remote account can only read a reporting view, its capabilities differ substantially from an account that can update operational tables. Review the effective remote identity and privileges, not merely whether the link is labeled public or private.

Global Naming and Link Names

When GLOBAL_NAMES is enabled, link naming must follow the remote global database name requirement. A friendly name such as raleigh_link is therefore not suitable for every installation. Check the existing policy rather than issuing an ALTER SYSTEM statement simply to make a tutorial example work.

The remote global database name, the requested service, and the naming alias may have related values, but they play different roles. Your administrator can confirm the appropriate link name. Short link names may also be expanded with a domain, so inspect the stored definition rather than assuming its displayed name will exactly match the short form you typed.

To inspect links owned by the current schema:

SELECT db_link, username, host
FROM user_db_links
ORDER BY db_link;

The HOST column contains the link's connection string, which can be a naming alias. Do not interpret that column name as proof that it always contains a literal machine hostname. Compare the definition with the documented naming configuration.

Apply the ABC Company Topology

The model helps a designer ask where information belongs and which departments need access to it. Suppose Headquarters needs monthly order summaries from Raleigh and Colorado Springs. That requirement suggests two independently configured outbound access paths from the reporting database. It does not imply that all four databases must connect to every other database.

Next, identify the direction, purpose, and permitted operations for each path. Headquarters might read regional summaries, while a regional service might have no reason to query Headquarters at all. Manufacturing may need product availability information rather than customer details. These distinctions help reduce unnecessary access and make dependencies visible.

The hierarchy is a useful planning aid, not a mandatory first step imposed by Oracle syntax. An organization can use a different topology and still create valid database links. The technical design should follow actual information-sharing requirements, available services, and administrative boundaries.

Performance and Failure Boundaries

Writing @raleigh_link makes remote access concise, but remote work still has network and availability costs. Request only the rows and columns the application needs. Test representative data volumes and review the distributed execution plan when a query joins local and remote objects. Do not assume every filter or join will execute at the location you expected.

A database link is not a replication system. Materialized views can use remote sources, but fast refresh has additional eligibility and logging requirements. Likewise, a transaction that updates multiple databases has coordination and failure considerations beyond a simple read-only query. Keep those topics separate from the basic connection example.

Consider what the reporting application should do if Raleigh is temporarily unavailable. A report might fail explicitly, retry according to a controlled policy, or use a separately designed cached dataset. A successful link definition alone does not choose that behavior. Define the application's response to partial availability before depending on remote queries in a critical workflow.

Verify One Layer at a Time

1. Check the local context. Confirm the schema, container, and link definition.
2. Check name resolution and reachability. Test from the originating database environment. TNSPING can assist with Oracle Net diagnostics, but it does not authenticate the remote account or execute a business query.
3. Check authentication. Confirm that the remote identity and connection policy are compatible.
4. Check object access. A working connection followed by an object-access error points to a different problem than an unresolved connection identifier.
5. Check representative SQL. Validate required results, expected row counts, latency, and behavior when the remote service is unavailable.

Record the exact error and failing step. Repeatedly recreating a link cannot fix a blocked network path, and changing a password cannot repair an incorrect service name. Keep the connection definition, privileges, and network configuration as separate evidence when diagnosing a failure.

Lesson Summary

Oracle 26ai References

• CREATE DATABASE LINK syntax and prerequisites
• Managing database links and global names
• Oracle Net naming methods