Database Link
2 min readA 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;
Environment:
Source Details
Hostname: server1.localdomain
DB Name: PROD
Schema name/password: scott/tiger
TNS Entry:
PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDIDEVED)
(SERVICE_NAME = PROD)
)
)
Target Details
Hostname: server2.localdomain
DB Name: DEV
Schema name/password: test/test
TNS Entry:
DEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDIDEVED)
(SERVICE_NAME = DEV)
)
)
Add TNS Entry
Add target db TNS entry in source database tnsnames.ora
DEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server2.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDIDEVED)
(SERVICE_NAME = DEV)
)
)
List db links
SQL> select * from dba_db_links;
no rows selected
Create PUBLIC database link
— create public db link On Source db PROD
We want to access the TEST schema objects (resides on DEV database) from
source DB (PROD Database)
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- ----------
PROD 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 DATABASE LINK test_remote
CONNECT TO test IDENTIFIED BY test
USING 'DEV'; 2 3
Database link created.
List database links
SQL> set lines 180 pages 999
SQL> col owner for a15
SQL> col DB_LINK for a15
SQL> col USERNAME for a15
SQL> col HOST for a39
SQL> col CREATION_DATE for a20
SQL> select owner, db_link, username, host , to_char(created,'MM/DD/YYYY
HH24:MI:SS') creation_date from dba_db_links;
Drop Public Database link
-- Please login as owner of db link
SQL> drop public database link TEST_REMOTE;
Database link dropped.
For more details, refer to the documentation:
