DATABASE LINK:
Purpose
Use the
CREATE DATABASE LINK statement
to create a database link. A database link is
a schema object in one database that enables you to access objects on another
database. The other database need not be an Oracle Database system. However, to
access non-Oracle systems you must use Oracle Heterogeneous Services.
After you have created a database
link, you can use it in SQL statements to refer to tables and views on the
other database by appending @dblink to the table or view name. You can query a table or view
on the other database with the
SELECT statement.
You can also access remote tables and views using any INSERT, UPDATE, DELETE,
or LOCK TABLE statement.
How to create
database link
Prerequisites
To create a private database link,
you must have the
CREATE DATABASE LINK system
privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system
privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.
Oracle Net must be installed on both
the local and remote Oracle databases.
Connect to TEST database as a Peter user.
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB11G READ WRITE
SQL> create database link mylink connect to peter identified by peter using 'test';
Database link created.
SQL> select * from emp@mylink;
ID NAME
---------- ---------
10 Ashwani
20 Rahul
30 Dilip\
30 Richard
SQL> --insert some data in emp table of user PETER in TEST database
SQL> select * from emp@mylink;
ID NAME
---------- ---------
10 Ashwani
20 Rahul
30 Dilip\
30 Richard
40 Augustin
