CREATE DATABASE LINK

Defining a public Database link:example

The following statement defines a shared public database link named remote this refers to the database specified by the SE Rvice 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 have APPROPRI Ate privileges):

UPDATE [email protected] 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-us Er 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 was created, HR can query tables in the schema HR on the local database in this manner:

SELECT * from [email protected];

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

INSERT into [email protected] (employee_id, last_name, email, hire_date, job_id) VALUES (999, ' Claus ', ' [email protected]', sysdate, ' Sh_clerk ');

UPDATE [email protected] SET min_salary = WHERE job_id = ' Sh_clerk ';

DELETE from [email protected] WHERE employee_id = 999;

You can create a synonym to hide the fact, a particular table is on the remote database. The following statement causes all 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 ;

CREATE DATABASE LINK