Skip to content

Oracle Database Link

An Oracle Database Link is a database object that allows a user to access and manipulate data on a remote database as if it were local.^[600-developer__database__oracle__oracle-db-link.md]

Definition and Creation

To establish a connection, a Database Link is created on the local database (referred to in source material as the "client") that points to the remote database^[600-developer__database__oracle__oracle-db-link.md]. The creation process requires specifying the link name, the remote username, the password, and the connection parameters.

The basic syntax involves using the CREATE DATABASE LINK statement, providing credentials with CONNECT TO ... IDENTIFIED BY ..., and defining the connection string with USING^[600-developer__database__oracle__oracle-db-link.md]. The USING clause typically encapsulates a TNS connection description, including the protocol (TCP), host IP, port (default 1521), and the service name^[600-developer__database__oracle__oracle-db-link.md].

Example Syntax

CREATE DATABASE LINK mypaycenter 
CONNECT TO mypaycenter IDENTIFIED BY `password`
USING '(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = `10.140.0.1`)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME =myPay)) )';
^[600-developer__database__oracle__oracle-db-link.md]

Usage

Once created, the Database Link enables querying and data manipulation across the network^[600-developer__database__oracle__oracle-db-link.md]. In SQL queries, the link is appended to the remote table name using the @ symbol (e.g., table_name@link_name^[600-developer__database__oracle__oracle-db-link.md]).

This functionality is frequently used for data recovery or migration tasks. For instance, data from a remote system can be retrieved and inserted into a local table using an INSERT INTO ... SELECT statement^[600-developer__database__oracle__oracle-db-link.md]. Complex queries can join local data with remote data or filter remote records before inserting them into the local database to ensure data integrity^[600-developer__database__oracle__oracle-db-link.md].

Sources

  • 600-developer__database__oracle__oracle-db-link.md