Database Links in Oracle

A database link (same as linked servers in SQL Server) is an object in one Oracle database that defines a path to another database. They allow you to query remote tables and execute remote procedures residing in an Oracle User on that remote Oracle database.  That way, you can use tables and other objects from that database in a SQL statement that you execute locally.  Say, for example, that you had a table called USER_MASTER in a remote database (ABC), and that you wanted to access that data while connected to your local database (XYZ) and based on the information that you get from that table, you need to decide what your processing logic is going to be. A database link is what you need.  Before creating it, you must collect the following information:

a) A net service name that your local database instance can use to connect to the remote instance, and

b) A valid username and password on the remote database.
The net service name is necessary for every database link.  The username and password that you specify when defining a database link are used to establish the connection to the remote instance.

The following statement creates a database link that allows access to a guest account that has previously been established on the ABC database:

CREATE DATABASE LINK TEST
CONNECT TO ABCUSER IDENTIFIED BY ABCPSWD
USING ‘ABCDB’;

The link name follows the LINK keyword, and is TEST. The CONNECT TO…IDENTIFIED BY clause specifies ABCUSER/ABCPSWD as the username and password to use when connecting to the remote instance. The USING clause specifies that the connection be established through the net service name ABCDB. Using the link, you can now query data in the remote database. For example:
SQL> SELECT *
2  FROM USER_MASTER@TEST
3  WHERE ….. -your selection criteria goes here

The @TEST following the table name in the SELECT statement indicates that the USER_MASTER table is in a remote database and should be accessed via the TEST link.

This was a very simplistic explanation on the database links in order to get you started.  In one of the future posts, we will take a look at the different types of links (public/private, the different authentication types – fixed, current user or connected, shared vs not shared links etc.).   Another thing to note is that just like linked servers in SQL Server, database links in Oracle are not just restricted to connecting to remote Oracle databases, you can also use them to connect to non-Oracle databases like connecting to SQL Server.  All you need is a driver to connect to these RDBMS.  If Oracle is running on windows, by installing the SQL Server client, you will get the ODBC driver and OLEDB providers that you need.  If Oracle is running on Unix, then there are two solutions:

a) either use Oracle Gateways, or

b) use third party drivers like the ones from Merant/Intersolve.