12
Jan

Database Link

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;

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top