How to create DB Link?

In this article, I’m going to show you how to create a DB Link step by step. How 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.

What is DB Link?

Typically, a database link has the same name as the global database name of the remote database that it references. For example, if the global database name of a database is ocp.oracle.com, then the database link is also called ocp.oracle.com. But that is applicable only in the case of if GLOBAL_NAME= TRUE if this parameter is false then it’s not required.

After creating a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Types of DB link

There are two types of DB Link, listed below.

• Private
• Public

Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access. So in this article we going with the public DB Link example.

To create a private database link, you must have the CREATE DATABASE LINK system privilege. The maximum number of database links that can be open in one session or one instance of a Real Application Clusters configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.

How to create a public DB link?

Suppose we have two databases with two different machines then use the below steps for creating a public DB Link.

DATABASE 1

Create a new user and grant some privileges, if the user already exists in your database then you don’t need to create any user just grant the privileges only.

SQL> create user ocp identified by ocp quota unlimited on users;
SQL> grant connect,resource to ocp;
SQL> conn ocp/ocp
SQL> create table t1 (id number, name varchar2(10));

After creating a table now we are going to insert a few records in the table.

SQL> insert into t1 values(10,'AARAV');
SQL> insert into t1 values(20,'SHRIPAL');
SQL> insert into t1 values(30,'SACHIN');
SQL> insert into t1 values(40,'VIKRAM');
SQL> insert into t1 values(50,'VIKASH');
SQL> commit;

Now check GLOBAL_NAME for your database using the following command.

SQL> select * from global_name;

GLOBAL_NAME
———————–
OCPTECH

In my case, GLOBAL_NAME is “OCPTECH“.

DATABASE 2

Now connect with DB2 on the second machine to create the DB Link. If you want to create a DB link in other schemas then you need to create a user otherwise you can create a DB link directly with sys users.

I am going to create a DB link using another schema, follow the below steps.

SQL> create user techlink identified by techlink quota unlimited on users;
SQL> grant connect,resource to techlink;
SQL> grant create database link to techlink;

Now create a DB link referenced by DATABASE 1.

SQL> conn techlink/techlink

SQL> create public database link db1Link connect to ocp identified by ocp
using ‘(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = rhel6.localdomain)(PORT = 1521))                        (CONNECT_DATA = (SERVICE_NAME = ocptech)))’;

Congrats your public DB link has been created successfully now you can use it.

How to use Database link

You can use DB Link using the below commands.

SQL> select * from ocp.t1@db1link;

PHONE            NAME
-----------   ------------
988852286      shripal
1234568792    aarav
54646546       delhi

Read - What is SQL, DBMS, and RDBMS