Wednesday, October 15, 2014

Explain DATABASE LINKS in oracle

Oracle databases have the ability to reference data that is stored outside of the local database.
To specify an access path to an object in a remote database, you will need to create a database link.  Database links can either be public (available to all accounts in that database) or private (created by a user for only that account’s use).  When you create a database link, you specify the name of the account to connect to, the password for the account, and the service name associated with the remote database.  If you do not specify an account name to connect to, then Oracle will attempt to use your local account name and password for the connection to the remote database.  In the following listing, a link name MY_LINK is created:
Create public database link Connect to <USERNAME> identified by <PASSWORD> Using ;
Create public database link MY_LINK Connect to HR identified by GODISGREAT Using ‘DB1’;
In this example, the link specifies that when it is used, it will open up a session in the database identified by the service named DB1.  when it open the session in the DB1 instance, it will log in as the user account HR, with the password “GODISGREAT”.  The service names for instances are stored in configuration files used by SQL*Net (the most recent version of SQL*Net is called Net8).  The configuration file for service names is called tnsnames.ora, and it specified the host, port, and instance associated with each service name.
To use this link for a table, it must be specified in the from clause, as in the following example :
Select * from EMPLOYEE@MY_LINK
This will access the EMPLOYEE table via the MY_LINK database link.  You can create a synonym for this table, as shown in the following SQL command:
Note that the fully qualified designation for the database object has been defined – its host and instance via its service name, it owner (HR), and its name (EMPLOYEE).
The location of the EMPLOYEE table is thus completely transparent to the end user.

No comments: