Skip to content

CREATE DATABASE LINK syntax

A Database Link (often abbreviated as DB Link) is a schema object in an Oracle database that allows a SQL query to access a remote database as if it were a local schema^[600-developer-database-oracle-oracle-db-link.md].

Syntax

The general syntax for creating a database link on a client machine involves specifying the link name, the remote username and password, and the connection string^[600-developer-database-oracle-oracle-db-link.md].

CREATE DATABASE LINK <link_name>
  CONNECT TO <remote_user> IDENTIFIED BY "<password>"
  USING '(DESCRIPTION=
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <ip_address>)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = <service_name>)
  )';

Parameters

  • CREATE DATABASE LINK: The command to initiate the creation of the link^[600-developer-database-oracle-oracle-db-link.md].
  • link_name: The name you wish to assign to this database link^[600-developer-database-oracle-oracle-db-link.md].
  • CONNECT TO ... IDENTIFIED BY: Credentials for the remote database user^[600-developer-database-oracle-oracle-db-link.md].
  • USING: The TNS connection description string containing the host IP, port, and service name^[600-developer-database-oracle-oracle-db-link.md].

Usage

Once created, the database link can be utilized to query or manipulate data on the remote server by appending the @link_name suffix to the table or view name^[600-developer-database-oracle-oracle-db-link.md].

For example, to retrieve data from a remote table: SELECT * FROM schema.table_name@my_db_link;^[600-developer-database-oracle-oracle-db-link.md]

Sources

  • 600-developer-database-oracle-oracle-db-link.md