CREATE DATABASE LINK

CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO
    { CURRENT_USER
    | user IDENTIFIED BY password [ dblink_authentication ]
    }
  | dblink_authentication
  ]...
  [ USING connect_string ] ;

 

Defining a Public Database Link: Example

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';

This database link allows user hr on the local database to update a table on the remote database (assuming hr has appropriate privileges):

UPDATE employees@remote    SET salary=salary*1.1    WHERE last_name = 'Baer';

Defining a Fixed-User Database Link: Example 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 password    USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.60)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ora11g.oracle.com)))';

After this database link is created, hr can query tables in the schema hr on the local database in this manner:

SELECT * FROM employees@local;

User hr can also use DML statements to modify data on the local database:

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

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

DELETE FROM employees@local    WHERE employee_id = 999;   

You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:

CREATE SYNONYM emp_table    FOR oe.employees@remote.us.example.com;