Oracle Database Link with example (for freshers)

What is Database Link?

Database link is to make a connection from one database to other or accessing the schema objects of remote database while connected to local database. Link is basically created between two physical servers and it provides you one way of communication. Through Database link, views and table can be accessed through select,insert,update,delete, or lock statement depending up on the permission that user have on the remote database.

Creating a database link:

As database link is created between 2 physical database, here i've 2 databases running on different machines with 1 is configured on ASM and other on file system. So both the database should be pingable to each other:

Here i've illustrated the DB link from scratch till viewing of data from remote db, So i've created 2 new users (1 in each DB):

SQL> create user tauseef1 identified by oracle default tablespace example quota unlimited on example temporary tablespace temp;

SQL> create user tauseef identified by oracle default tablespace example quota unlimited on example temporary tablespace temp;

Granting permission to Users:

SQL> grant connect to tauseef1;

SQL> grant create session to tauseef1;

SQL> grant create table to tauseef1;

Listener should be running on both databases, checking the listener status:

$lsnrctl status listener

Users are created and listener are up and running on both databases, no we have to configure tns entry for local net service configuration by running netca all the images are self explaintory:

Give the service name:

You can write down the hostname if there is DNS server and it resolving the IP but if there is no DNS server, you should write the ip of remote DB and should be pingable:

Press finish, TNS entry will be create on tnsnames.ora file located at Cd $ORACLE_HOME/network/admin.

TNS entry with name db11g has been created, we can check its connectivity by command:

$ tnsping db11g

Now Its all set for creation of DB link to remote DB with local name service configuration db11g, in the mean while we will create 1 table on remote DB in new created schema "Tauseef1" with name test1 and will perform some insert data operations:

SQL> create table test1 (No number(10),Name varchar2(20),email varchar2(30));

SQL> insert into test1 values(10,'Tauseef Alam','tauseefalam25@gmail.com');

SQL> insert into test1 values(25,'Tauseef','tauseefalam25@gmail.com');

SQL> insert into test1 values(48,'Tauseef khan','tauseefalam25@gmail.com');

SQL>  commit;

Now its fun time, let create the database link on local database in our newly created schema "Tauseef" but before creation of DB link, we have to grant Tauseef, create database link permessions:

SQL> grant create database link to tauseef;

SQL> create database link testfs connect to tauseef1 identified by oracle using 'db11g';

Database link with name "testfs" has been created from DB1 to DB2, lets try to access the data of remote database:

SQL>   select * from test1@testfs;

 We have viewed the test1 table data created on DB in tauseef1 schema while connecting to user tauseef from DB1.