- 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.
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';
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> grant connect to C##TWO; Grant succeeded.
— 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.
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> 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;