How to create database link

How to create database link

In this article, I’m going to show you how to create database link step by step.

How to create 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.

You also like this RMAN article.

Oracle Data Pump Advance tutorial Click here

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 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 on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

A database link is two types:
  • 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.

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 public DB link?

suppose we have two databases with two different machines. use below steps.

In DB1.




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.

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

Now insert a few records in the above 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 followoing command.

SQL> select * from global_name;

GLOBAL_NAME
———————–
OCPTECH

In my case, GLOBAL_NAME is “OCPTECH“.

In DB2.

Now connect with DB2 on the second machine to creating the DB Link.

If you want to create DB link in other schemas then you need to create a user otherwise you can create DB link directly with sys users.

I am going to create DB link using another schema, follow 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 DB link with referenced by DB1.

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.

SQL> select * from ocp.t1@db1link;

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

Hope you found this article very informative, if yes please write a comment in the comment box.

DataGuard manual failover watch video.




Share

2 thoughts on “How to create database link”

Leave a Comment

Share