What is a database link?
A database link is a pointer that defines a one-way communication path from one Oracle Database to another database. A database link allows local users to access data on a remote database.
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.
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 = 192.168.1.106) (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 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:
create database link DEVTOPORTAL.192.168.1.106 connect to portal identified by "portal123" using 'Portal_New';
Once this database link is created, you can query tables in the schema PORTAL on the remote database in this manner:
SELECT * FROM portal.emp@DEVTOPORTAL.192.168.1.106;
How to check database link:
select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
APPS devtoportal.192.168.1.106 PORTAL portal_new 29-APR-14
TESTING:
SQL> select * from dual@”devtoportal.192.168.1.106";
D
-
X
Dropping a database link
drop database link;
drop database link DEVTOPORTAL.192.168.1.106;