Database Links

What Are Database Links?

A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.

A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A, then they must define a link that is stored in the data dictionary of database B.

A database link connection allows local users to access data on a remote database. For this connection to occur, each database in the distributed system must have a unique global database name in the network domain. The global database name uniquely identifies a database server in a distributed system.

Figure 31-3 shows an example of user scott accessing the emp table on the remote database with the global name hq.example.com:

Figure 31-3 Database Link

Description of "Figure 31-3 Database Link"

Database links are either private or public. If they are private, then only the user who created the link has access; if they are public, then all database users have access.

One principal difference among database links is the way that connections to a remote database occur. Users access a remote database through the following types of links:

Create database links using the CREATE DATABASE LINK statement. After a link is created, you can use it to specify schema objects in SQL statements.

What Are Shared Database Links?

A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.

When a local database is connected to a remote database through a database link, either database can run in dedicated or shared server mode. The following table illustrates the possibilities:

A shared database link can exist in any of these four configurations. Shared links differ from standard database links in the following ways:

• Different users accessing the same schema object through a database link can share a network connection.

• When a user must establish a connection to a remote server from a particular server process, the process can reuse connections already established to the remote server. The reuse of the connection can occur if the connection was established on the same server process with the same database link, possibly in a different session. In a non-shared database link, a connection is not shared across multiple sessions.

• When you use a shared database link in a shared server configuration, a network connection is established directly out of the shared server process in the local server. For a non-shared database link on a local shared server, this connection would have been established through the local dispatcher, requiring context switches for the local dispatcher, and requiring data to go through the dispatcher.

  See Also:

  Oracle Database Net Services Administrator's Guide for information about shared server

Why Use Database Links?

The great advantage of database links is that they allow users to access another user's objects in a remote database so that they are bounded by the privilege set of the object owner. In other words, a local user can access a link to a remote database without having to be a user on the remote database.

For example, assume that employees submit expense reports to Accounts Payable (A/P), and further suppose that a user using an A/P application must retrieve information about employees from the hq database. The A/P users should be able to connect to the hq database and execute a stored procedure in the remote hq database that retrieves the desired information. The A/P users should not need to be hq database users to do their jobs; they should only be able to access hq information in a controlled way as limited by the procedure.

Global Database Names in Database Links

To understand how a database link works, you must first understand what a global database name is. Each database in a distributed database is uniquely identified by its global database name. The database forms a global database name by prefixing the database network domain, specified by the DB_DOMAIN initialization parameter at database creation, with the individual database name, specified by the DB_NAME initialization parameter.

For example, Figure 31-4 illustrates a representative hierarchical arrangement of databases throughout a network.

Figure 31-4 Hierarchical Arrangement of Networked Databases

Description of "Figure 31-4 Hierarchical Arrangement of Networked Databases"

The name of a database is formed by starting at the leaf of the tree and following a path to the root. For example, the mfg database is in division3 of the example_tools branch of the com domain. The global database name for mfg is created by concatenating the nodes in the tree as follows:

• mfg.division3.example_tools.com

While several databases can share an individual name, each database must have a unique global database name. For example, the network domains us.americas.example_auto.com and uk.europe.example_auto.com each contain a sales database. The global database naming system distinguishes the sales database in the americas division from the sales database in the europe division as follows:

• sales.us.americas.example_auto.com

• sales.uk.europe.example_auto.com

  See Also:

  "Managing Global Names in a Distributed System" to learn how to specify and change global database names

SELECT * FROM hr.employees@db1.example.com;

Names for Database Links

Typically, a database link has the same name as the global database name of the remote database that it references. For example, if the global database name of a database is sales.us.example.com, then the database link is also called sales.us.example.com.

When you set the initialization parameter GLOBAL_NAMES to TRUE, the database ensures that the name of the database link is the same as the global database name of the remote database. For example, if the global database name for hq is hq.example.com, and GLOBAL_NAMES is TRUE, then the link name must be called hq.example.com. Note that the database checks the domain part of the global database name as stored in the data dictionary, not the DB_DOMAIN setting in the initialization parameter file (see "Changing the Domain in a Global Database Name").

If you set the initialization parameter GLOBAL_NAMES to FALSE, then you are not required to use global naming. You can then name the database link whatever you want. For example, you can name a database link to hq.example.com as foo.

After you have enabled global naming, database links are essentially transparent to users of a distributed database because the name of a database link is the same as the global name of the database to which the link points. For example, the following statement creates a database link in the local database to remote database sales:

CREATE PUBLIC DATABASE LINK sales.division3.example.com USING 'sales1';

Types of Database Links

Oracle Database lets you create private, public, and global database links. These basic link types differ according to which users are allowed access to the remote database:

Determining the type of database links to employ in a distributed database depends on the specific requirements of the applications using the system. Consider these features when making your choice:

Users of Database Links

When creating the link, you determine which user should connect to the remote database to access the data. The following table explains the differences among the categories of users involved in database links:

Creation of Database Links: Examples

Create database links using the CREATE DATABASE LINK statement. The table gives examples of SQL statements that create database links in a local database to the remote sales.us.americas.example_auto.com database:

Schema Objects and Database Links

After you have created a database link, you can execute SQL statements that access objects on the remote database. For example, to access remote object emp using database link foo, you can issue:

SELECT * FROM emp@foo;

You must also be authorized in the remote database to access specific remote objects.

Constructing properly formed object names using database links is an essential aspect of data manipulation in distributed systems.

SELECT * FROM scott.emp@sales.division3.example.com;  # emp table in scott's schema
SELECT loc FROM scott.dept@sales.division3.example.com;

SELECT name FROM scott.emp@foo;  # link name different from global name

SELECT * FROM emp@hq.example.com;

SELECT * FROM emp;

CONNECT SYSTEM@sales1

SELECT * FROM scott.emp@hq.example.com;
INSERT INTO jane.accounts@hq.example.com (acc_no, acc_name, balance)
  VALUES (5001, 'BOWER', 2000);
UPDATE jane.accounts@hq.example.com
  SET balance = balance + 500;
DELETE FROM jane.accounts@hq.example.com
  WHERE acc_name = 'BOWER';

Database Link Restrictions

You cannot perform the following operations using database links:

• Grant privileges on remote objects

• Execute DESCRIBE operations on some remote objects. The following remote objects, however, do support DESCRIBE operations:

  • Tables

  • Views

  • Procedures

  • Functions

• Analyze remote objects

• Define or enforce referential integrity

• Grant roles to users in a remote database

• Obtain nondefault roles on a remote database. For example, if jane connects to the local database and executes a stored procedure that uses a fixed user link connecting as scott, jane receives scott's default roles on the remote database. Jane cannot issue SET ROLE to obtain a nondefault role.

• Use a current user link without authentication through SSL, password, or NT native authentication