How to create Oracle database link with example?

In my previous article I have given the different PL/SQL examples which are very useful in real life. In this article I would like to throw light on Oracle database link with examples. The Oracle database link is most importantly used to transfer or used to perform database operations from one user to another user with ease. These kind of techniques are specifically useful in oracle database migration. To check or compare the data from different databases. In this article I would like to give the correct information about how to create Oracle database link with its usages in detail.

What you will find in this article?

What is Oracle database link with example?

How to create Oracle database link?

What are usages of database link?

What is Oracle database link ?

In this section I would like to give you heads-up on oracle database link. The database link is nothing but the connection from oracle database to another remote database. The question in mind that where the database link is been used? The database link is used to perform db operations from remote database. The remote database will be any oracle database or any ODBC complaint database which will be mysql or SQL server.

If you can see above diagram the local database is directly communicating with the remote database. The remote database contains table and user can access those tables through local database and database link. In next section i would like to give you the details about how to create db link and its examples.

How to create oracle database link and its examples?

In this section i would like to explain more about the oracle database link and its examples. There are two types of database links; Public db links and private db links.

1.Public Database links : The public database links are useful and accessible public to all users of that database.

2.Private Database links : Private database links are most used database links as only user of that db link have access for private database links. These kind of db links are very useful database by considering the security constraint.

Syntax to create db link :

CREATE DATABASE LINK dblink_name

CONNECT TO remote_user_name IDENTIFIED BY password

USING ‘remote_database_name/Connection String’;

Step 1 : We require to write the name of the database link. The name of db link is required to access data from remote database.

Step 2 : Connect tp clause is very important clause which gives user access to connect to remote database nam*** Note : Here we require to check the ports are open from source to target else open port of target database

Step 3 : IDENTIFIED BY clause is used to give the password of target database. User should know the password of target database.

Step 4 : USING clause is used for the DB link connectivity whether it is connecting to remote database or any local database. If you specify only the database name, Oracle will append the database domain to the connect string to form a complete service name.

***Note : You require to add entry of remote database or target database in tnsnames.ora file.

Real Life Example :

If you have recently migrated data from User ‘ComplexSQL_SQL’ to Oracle database User ComplexSQL and you require to check the data from ComplexSQL user for ComplexSQL_SQL user. Create database link in that case ,

Kindly fire following query to ComplexSQL user and make sure that ports are open for ComplexSQL_SQL remote server,

CREATE DATABASE LINK dblk_Complexsql

CONNECT TO ComplexSQL_SQL IDENTIFIED BY XXXXXX

USING ‘(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=complexsql.db.com)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=complexsql)) )’;

If you want to create public database link then you can create that using public keyword,

How to access data from remote database using DB link?

The key use of database link is it allows the user to access multiple objects from remote database and to access those there are few standard syntax.

Sytax to access table or view :

table_name/view_name@database_link

As example if you want to access the table from remote db from local database kindly follow following syntax

SELECT * FROM remote_table_name/remote_View_name@database_link;

Example :

We already created the database link on ComplexSQL user. If you wanted to access Employee table from remote database use,

Select * from Employee@dblk_Complexsql;

The above query will fetch the data from employee table on remote database.

You can also create synonyms to access the data from remote environment/server.

CREATE SYNONYM local_table

FOR remote_table@database_link;

CREATE SYNONYM Employee_bck

FOR Employee@dblk_Complexsql;

The above statement will create synonym and you can access data directly rather than using dblink name again and again,

Select * from Employee_bck;

You can also insert the data in remote database,

INSERT INTO Employee@dblk_Complexsql(emp_id, name, email) VALUES(1,’Amit’,’contact@complexsql.com’);

Things to remember and usages of db link :

1. Database Migration : If you wanted to check the migrated data from remote server you need to have access of remote database and rather than connecting again and again to remote database you can create db link.
2. PLSQL code for external database : There are so many times we require to verify the data from external databases. In that case you can create synonym using db link and use that synonym in PL SQL code.
3. Verifications : If you want to check communication between multiple databases you can use DB link.

Things to remember :

1. Security : For security reason you can create seperate user for remote database for db link creation and never give to anyone.
2. Naming convension : The naming convention for db link should be proper and by looking at the name user should understand the purpose of the db link
3. TNSNAMES.ORA : You need to add entry for remote database in tnsnames.ora file. This is most important step.

These are few things to remember related to database link. Hope you understand the concept of database link easily and if you have any questions or queries regarding the same kindly comment in comments section.