CREATE DATABASE LINK

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 ‘local‘;

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‘, ‘[email protected]‘, 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;