Database Links

Database Links:

---------------

A Database Link allows you to access data in a remote database as if it is a local table's data.

The connection can be made to a database hosted on the same machine or remotely on another

server.When you query data on a different database, you are essentially using a distributed query.It is also possible to execute distributed transactions through database links like insert,

update and delete.The authentication used in a database link is either the link's owner or a hard-coded usernameand password, depending on the way the link was created.

A database link in oracle can be either private (for use only by the link's owner) or public(accessible to any user with the correct privileges).Connectivity is established via Sql*Net, using an alias defined in the local database server's tnsnames.ora.

Example:

----------

export TNS_ADMIN=$ORACLE_HOME/network/admin

cd $TNS_ADMIN

vi tnsnames.ora

-- add the entries of target database that needs to be accessed

eg;

TEST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = hyderabad.oracle.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = TEST)

)

)

tnsping TEST -- it should work - if not please listener on the target server.

sqlplus '/as sysdba'

SQL>CREATE DATABASE LINK LINK_TEST CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'TEST';

SQL>SELECT * FROM DBA_DB_LINKS;

SQL>SELECT * FROM ALL_DB_LINKS;

SQL>select * from BONUS@LINK_TEST; - It shud give the results of target DB

SQL>DROP DATABASE LINK LINK_TEST; -- To Drop Database Link

TO Create Pubic Database link

PUBLIC database link is a DB link which can acceese by All the users in the Database.

Syntax to create a PUBLIC database:

SQL>CREATE PUBLIC DATABASE LINK LINK_TEST CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'TEST';

Syntax to drop a PUBLIC database:

SQL>Drop PUBLIC DATABASE LINK LINK_TEST;