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;