Monday, 27 December 2010

Creating a database link to access objects on a remote database

1. service name in tnsname
2. create a database link on the local database
3.connect using this database link to remote database

Service Name
To create a database link that points to a remote database you should have proper service name entry in local computer's tnsnames.ora file

Adding service name details in the tnsnames file to access remote database
(if this is already existing ignore it)

ex: tns entry to connect to a remote database running on testserver using service name 'test'

test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = testserver)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

create database link on the local database (the database from which you are trying to access another database)

Syntax:

create database link link_name connect to user_name identified by password using service_name

link_name = this is the name for the database link
username = username on the remote database. this username will be used to connect to remote             database
password = password for the user specified
service_name = this is the service name that is in the local database's tnsnames.ora file.

Ex:

create database link 'testlink' connect to hr identified by hr using 'test'


Accessing objects on remote

remoteobjetct_name@dblink_name

select * from employees@testlink;