What is an Oracle database link

A database link is a connection from the Oracle database to another remote database. The remote database can be an Oracle Database or any ODBC compliant database such as SQL Server or MySQL.

CREATE DATABASE LINK dblink
CONNECT TO remote_user IDENTIFIED BY password
USING 'remote_database';

CREATE DATABASE LINK REMOTE_TESCO                                  -- OK
    CONNECT TO DALERP IDENTIFIED BY tesco
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=ip)(PORT=1521))
                (CONNECT_DATA=(SERVICE_NAME=tescopdbs))
            )';

CREATE or replace PUBLIC DATABASE LINK REMOTE_TESCO 
    CONNECT TO DALERP IDENTIFIED BY TESCO
    USING 'tescopdbs';

Defining a Public Database Link:

The following statement defines a shared public database link named remote that refers to the database specified by the service name remote:

CREATE PUBLIC DATABASE LINK remote 
   USING 'remote'; 
   
   
UPDATE employees@remote
   SET salary=salary*1.1
   WHERE last_name = 'Baer';

Defining a Fixed-User Database Link:

In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:

CREATE DATABASE LINK local 
   CONNECT TO hr IDENTIFIED BY hr
   USING 'local';


SELECT * FROM employees@local;
INSERT INTO employees@local
   (employee_id, last_name, email, hire_date, job_id)
   VALUES (999, 'Claus', 'sclaus@oracle.com', SYSDATE, 'SH_CLERK');

UPDATE jobs@local SET min_salary = 3000
   WHERE job_id = 'SH_CLERK';

DELETE FROM employees@local 
   WHERE employee_id = 999;

Defining a CURRENT_USER Database Link

The following statement defines a current-user database link to the remote database, using the entire service name as the link name:

CREATE DATABASE LINK remote.us.oracle.com
   CONNECT TO CURRENT_USER
   USING 'remote';
    
CREATE SYNONYM emp_table 
   FOR oe.employees@remote.us.oracle.com;