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;