Oracle Database Link (DBLINK)

How to Create Oracle Database Link

Topic Introduction: Here discuss the Database link where we can connect one database to another remote database. and can manipulate data from a remote database.  In this example, we will create a database link to a remote Oracle Database server located on server 192.168.3.1 with port 1521 and service name SALES.

First, add the following entry to tnsnames.ora file in the local Oracle Database server. Typically, the tnsnames.ora is located in the directory /NETWORK/ADMIN/ under ORACLE_HOME:

SALES =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.1)(PORT = 1521))

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SERVICE_NAME = ORCL)

    )

)

Database Link Create General Structure:

CREATE DATABASE LINK dblink

CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_database';

Database Link Create name sales:

CREATE DATABASE LINK sales 

    CONNECT TO oe IDENTIFIED BY oe

    USING 'SALES';

Synonym Create using Database Link:

    

CREATE SYNONYM local_table

FOR remote_table@database_link; 

   

SELECT * FROM customers@sales;

Data Insert Using Database Link:

INSERT INTO customers@sales(customer_id, name, email)

VALUES(3,'Test Customer','contact@testc.com');    

Database Link Password Change/ Alter Database link Statement:

ALTER DATABASE LINK sales 

CONNECT TO oe IDENTIFIED BY 123;