Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

Oracle Database Link

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
oracle database
  • User AvatarKiran Dalvi
  • 21 Nov, 2021
  • 0 Comments
  • 5 Mins Read

Oracle Database Link

How to create oracle Database Link [DB link] ?

Database link

  1. Overview
  2. Environment
  3. Add TNS Entry
  4. List db links
  5. Create PUBLIC db link
  6. Create PRIVATE db link
  7. List db links again
  8. Verify the db link results
  9. Drop Public Database link
  10. 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

1
2
3
4
5
6
7
8
9
10
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:

1
2
3
4
5
6
7
8
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

1
2
3
4
5
6
7
8
BATCH46 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx ) (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BATCH46)
    )
  )

 

 

4. List db links

1
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) 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

1
2
3
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]

1
2
3
4
5
6
SQL> conn C##ABC; <-- If you don't know password then use proxy user . PROXY USER
Connected.
SQL>
 
SQL> CONN C##ABC/abc123
Connected.

.

1
2
3
4
5
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

1
2
3
4
5
6
7
8
9
10
11
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 —

1
2
3
4
5
-- 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

1
2
3
4
5
6
7
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]

1
2
3
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

1
2
3
4
5
6
7
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]

1
2
3
4
5
6
7
8
9
10
11
12
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;