Kiran Dalvi
21 Nov, 2021
0 Comments
2 Mins Read

Database Link

How to create oracle Database Link [DB link] ?

Database link

Overview
Environment
Add TNS Entry
List db links
Create PUBLIC db link
Create PRIVATE db link
List db links again
Verify the db link results
Drop Public Database link
Drop Private Database link

1.Overview

A database link (DBlink) is a definition of how to establish a connection from one Oracle database to another.

Type of Database Links:

Private database link – belongs to a specific schema of a database. Only the owner of a private database link can use it.

Public database link – all users in the database can use it.

Global database link – defined in an OID or Oracle Names Server. Anyone on the network can use it.

How to find Global name? SELECT * FROM global_name;

2.Environment

Source Details

Hostname: 192.168.0.197

DB Name: TESTDB

Schema name/password: C##SCOTT/abc123

TNS Entry: 

TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.197)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB)
    )
  )

Target Details

Hostname: xx.xxx.xxx.xx

DB Name: BATCH46

Schema name/password: NASREEN/abc123

TNS Entry:

BATCH46 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
    )
  )

3.Add TNS Entry

Add target db TNS entry in source database tnsnames.ora

BATCH46 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx ) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
    )
  )

4. List db links

SQL> select * from dba_db_links;

5. Create PUBLIC database link

— create public db link On Source db TESTDB

We want to access the NASREEN schema objects (resides on BATCH46 database) from source db (TESTDB Database)

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
TESTDB     READ WRITE

SQL> show user
USER is "SYS"
SQL>

CREATE PUBLIC DATABASE LINK link_name
CONNECT TO remote_user_name
IDENTIFIED BY remote_user_password
USING 'remote_service_name';


SQL>

— Create Public db link without modify TNS entry

CREATE PUBLIC DATABASE LINK link_three
 CONNECT TO NASREEN IDENTIFIED BY abc123
 using
 ' (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
    )
  )'
  /

SQL> show user
USER is "SYS"

— OR —

— Create PUBLIC DB Link Using EASY CONNECT

CREATE PUBLIC DATABASE LINK link_five
CONNECT TO NASREEN IDENTIFIED BY abc123
USING 'xx.xxx.xxx.xx:1521/BATCH46';

6.Create PRIVATE database Link

Private database link belongs to a specific schema of a database. Only the owner of a private database link can use it.

[php] CREATE DATABASE LINK link_name CONNECT TO remote_user_name IDENTIFIED BY remote_user_password USING ‘remote_service_name’; SQL> create user C##ABC identified by abc123; User created. SQL> GRANT CONNECT TO C##ABC; Grant succeeded. SQL> grant create database link to C##ABC;; Grant succeeded. [php]

SQL> conn C##ABC; <-- If you don't know password then use proxy user . PROXY USER
Connected.
SQL>

SQL> CONN C##ABC/abc123
Connected.

SQL> CREATE DATABASE LINK LINK_PRIVATE1
   CONNECT TO NASREEN IDENTIFIED BY abc123
   USING 'BATCH46';
  2    3
Database link created.

— OR —

— Create PRIVATE db link without modify TNS entry

CREATE DATABASE LINK LINK_PRIVATE2
 CONNECT TO NASREEN IDENTIFIED BY abc123
 using
 '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
  )
  )'
  /

— OR —

-- Create DB Link Using EASY CONNECT

CREATE DATABASE LINK LINK_PRIVATE3
CONNECT TO NASREEN IDENTIFIED BY abc123
USING 'xx.xxx.xxx.xx:1521/BATCH46';

SQL> SQL> conn / as sysdba Connected. SQL> revoke create database link from C##ABC; <---- [/php]

7. List database links again

set lines 180 pages 999
col owner for a15
col DB_LINK for a15
col USERNAME for a15
col HOST for a39
col CREATION_DATE for a20
select owner, db_link, username,host ,to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date from dba_db_links;

8. Verify the db link results

— Since it is public db link any user in source database can access the TEST schema objects of targert db

— Even new user can access. eg…. create new user <—-

— verfiy public db links —

SQL> create user C##TWO identified by one; <---- User created. [/php]

SQL> grant connect to C##TWO;

Grant succeeded.

SQL> conn C##TWO/two; <---- Connected. SQL> select count(*) from employee@link_two; <-- with TNS entry COUNT(*) ---------- 2 [/php]

SQL> select count(*) from employee@link_three; <-- with TNS Connect String COUNT(*) ---------- 2 [/php]

SQL> select count(*) from employee@link_five; <-- Easy connect string COUNT(*) ---------- 2 [/php]

— verfiy private db links —

Private database link belongs to a specific schema of a database.

Please note only the owner of a private database link can use it.

SQL> conn C##ABC/abc123; Connected. SQL> select count(*) from employee@LINK_PRIVATE1; <-- With TNS Entry COUNT(*) ---------- 2 [/php]

SQL> select count(*) from employee@LINK_PRIVATE2;<-- TNS connect string COUNT(*) ---------- 2 [/php]

SQL> select count(*) from employee@LINK_PRIVATE3; <-- Easy connect COUNT(*) ---------- 2 [/php]

9.Drop Public Database link

— Please login as owner of db link

drop public database link LINK_TWO;

drop public database link LINK_THREE;

 drop public database link LINK_FOUR;

drop public database link LINK_FIVE;

10.Drop Private Database link

— Please login as owner of db link

SQL> conn C##ABC/abc123; <---- Connected. drop database link LINK_PRIVATE1; drop database link LINK_PRIVATE2; drop database link LINK_PRIVATE3; [/php]

SQL>

SQL> conn / as sysdba
Connected.

set lines 180 pages 999
col owner for a15
col DB_LINK for a15
col USERNAME for a15
col HOST for a39
col CREATION_DATE for a20
select owner, db_link, username,host ,to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date from dba_db_links;

Database Link

How to create oracle Database Link [DB link] ?

Join Our Free Webinar Today!

Explore Insights, Trends, and Expert Advice

About Us

Explore

Useful Links

Contact Info