Database Link in Oracle

Back to: Oracle DBA Tutorials

Database Link in Oracle with Examples

In this article, I am going to discuss Database Link in Oracle with Examples. Please read our previous article where we discussed Oracle Net Manager with Examples. We are learning about a new concept called Database link. This database link connects one database to another database.

What is a Database link?

A database link is a schema object in one database that enables you to access objects in another database. We have two databases named PROD1 and PROD2. If the database prod1 needs to access objects in prod2, we have to create a database link that allows us to access objects in prod2.

The other database need not be an oracle database system. This indicates that we can create a database link between two oracle databases. We can also create database links between oracle database and other platform databases like Mysql, SQL server, and Sybase databases. However, to access non-oracle databases you must use Oracle Heterogeneous Services.

After we have created a database link, we can use it in SQL statements to refer to tables, views, and PL/SQL objects in the other database by appending @dblinkname to the table, view, and PL/SQL object name.

To create a private database link, you must have CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. We will learn about private database links and the public database with an example.

Example to Understand Database Link in Oracle:

From the image, we can see we have two databases called PROD1 and PROD2. In the PROD1 database, we have a user called prod1_admin, in the PROD2 database, we have a user called prod2_admin. The user prod2_admin has a table called employees. The user prod1_admin in the PROD1 database needs to access the employees table in the PROD2 database. In order to access the employees in the PROD2 table we have to create a database link in the PROD1 database.

Let us assume that we have created a database link as read_prod2. The user prod1_admin needs to access the table in prod2_admin, then prod1_admin needs to write the following query.

Query: select * from employees@read_prod2;

This query indicates that @read_prod2 is used to access the tables in another database.

How to create a database link?

We have a simple create statement which allows us to create database links.
Statement:
CREATE DATABASE LINK <database_link_name>
connect to <user> identified by <password>
using ‘<connect_String_for_remote_db>’;

From our user perspective, we form a statement as follows:
Create database link read_prod2
connect to prod2_admin identified by goodluck
using ‘PROD2’;

In order to create a database link, we must know the password of the user with that we are going to create database links. We have a data dictionary view called dba_db_links which is a view for database links.

In the next article, I am going to discuss CREATE DATABASE LINK in Oracle with Examples. Here, in this article, I try to explain Database Link in Oracle with Examples and I hope you enjoy this Database Link in Oracle with Examples article.