What is Database Link ?

A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. A database link is a schema object in one database that enables you to access objects on another database.

To create a private database link, we must have the create database link system privilege. To create a public database link, we  must have the  create public database link  system privilege. Also, we must have the CREATE SESSION system privilege on the remote Oracle database . Before creating it, we must collect the following information:

1.)  A net service name that our local database instance can use to connect to the remote instance and
2.) A valid username and password on the remote database.

The net service name is necessary for every database link. The username and password that we specify when defining a database link are used to establish the connection to the remote instance. The Credentials of database links are : 

Primary Server   =  Noida 

Remote  Server =  Delhi 


1.) Connect to datbase as 
C:\> sqlplus sys/xxxx@delhi as sysdba 


2.) Create a user
SQL> create user abc identified by abc
  2  default tablespace users
  3  quota unlimited on users;
User created.


3.) Grant the privileges required for database link 
SQL> grant create public database link , create session, create table  to abc;
Grant succeeded.


4.) Connect with "ABC" user and create a table for testing purpose as
SQL> conn abc/abc@delhi
Connected.
SQL> create table test1 (id number);
Table created.
SQL> insert into test1 values(&T);
Enter value for t: 23
old   1: insert into test1 values(&T)
new   1: insert into test1 values(23)
1 row created.
SQL> / 
Enter value for t: 345
old   1: insert into test1 values(&T)
new   1: insert into test1 values(345)
1 row created.
SQL> / 
Enter value for t: 32
old   1: insert into test1 values(&T)
new   1: insert into test1 values(32)
1 row created.
SQL> commit ;
Commit complete.


SQL> select * from test1 ; 
         ID
----------
        23
       345
        32
SQL> exit


5.) Connect with primary database as
c:\>sqlplus sys/XXXX@noida as sysdba 


6.) Create a Public database link and access the remote table(test1) as
SQL> create public database link d_link connect to abc identified by abc  using  'DELHI' ; 
Database link created 


SQL> select * from abc.test1@d_link ; 
        ID
----------
        23
       345
        32


Hence , we access the remote table by using the database link .


Enjoy     :-)