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.

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.

  • 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.

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.

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

3 thoughts on “How to create DB Link?”

Leave a Comment