Create database link

Database Link

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

The following link types are supported:

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.

What is a database link?

A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. A database link connection allows local users to access data on a remote database.

Prerequisites:

To create a database link, you must have the CREATE DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

In the following example:
There are two databases: DEV and PORTAL
We are creating a db link on DEV so that we can acces the tables of portal user from DEV database.

Make TNS Entries on both databases

dev =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.1.106)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev)
)
)

Portal_New =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 192.168.1.115)
(PORT = 1521)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = portal)
)
)

Check TNS on both databases

From portal database
C:\Documents and Settings\Administrator>tnsping dev

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 – Production on 16-SEP-2
014 23:20:52

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
C:\oracle11g\product\11.1.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.254.
0.206) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev))
)
OK (10 msec)

From dev database
oratst#tnsping portal_new

TNS Ping Utility for HPUX: Version 10.2.0.3.0 – Production on 17-SEP-2014 12:45:30

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
/Clone/db03/oracle/10R2/network/admin/DEV_oratst/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.115) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = portal)))
OK (110 msec)

connect to dev database from apps user:
sqlplus apps/apps

Creating a Private Database Link:

create database link db link name connect to username identified by “password” using ‘tns name’;

In the following statement, user portal on the remote database defines a fixed-user database link named Portal_New to the portal schema on the local database:

Once this database link is created, you can query tables in the schema PORTAL on the remote database in this manner:

How to check DB link:

TESTING:

For dropping a database link.

drop database link;