Oracle7 Server Distributed Systems Volume I: Distributed Data

Database Links

Oracle uses database links to facilitate connections between the individual databases of a distributed system. A database link defines a path to a remote database by uniquely identifying and specifying the location of a remote database.

Note: Remember that a global database link is created automatically for each database defined in Network Manager. However, public and private database links are typically created by users or database administrators.

Database Link Name same as Global Database Name

A database link defines a path to a remote database. The two components of a path are a remote account and a database string. Database links are essentially transparent to users of a distributed system, 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. The database link named SALES.DIVISION3.ACME.COM describes a path to a remote database of the same name:

CREATE PUBLIC DATABASE LINK sales.division3.acme.com 
USING 'sales.division3.acme.com'

At this point, any application or user connected to the local database can access data in the SALES database by using the global object name (SALES.DIVISION3.ACME.COM). The SALES.DIVISION3.ACME.COM database link implicitly facilitates the connection to the SALES database. For example, consider the following remote query that references the remote table SCOTT.EMP in the SALES database:

SELECT * FROM scott.emp@sales.division3.acme.com;

National Language Support (NLS) and Database Links

When a user session connects to an instance, the values of NLS parameters used by the instance for that user session are defined by the value of the initialization parameter NLS_LANG for that session. This applies to direct and indirect connections.

If the values of the NLS parameters are changed during a session by an ALTER SESSION statement, the changes are automatically propagated to all instances to which the user session is connected, either directly or indirectly. For more information on National Language Support features, see the Oracle7 Server SQL Reference.

Types of Database Links

Oracle uses several types of database links to resolve users' references to global object names:

private database link Created on behalf of a specific user. A private database link can be used when the owner of the link specifies a global object name in a SQL statement, or in the definition of the owner's views or procedures.
public database link Created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
global database link Created and managed by a global naming service such as Oracle Names. A global database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

Public and private database links are stored in the data dictionary of a database. Global database links are not.

Each type of database link has advantages and disadvantages, as compared to the other types. For example, you can maintain tighter security with private than with public or global database links. The use of a private database link is at the discretion of the owner of the link.

Database Links in SQL Statements

The owner of a private database link can use the link in his/her own SQL statements and selectively allow other users to use the private link by creating views, procedures, or synonyms that reference the link in their definitions. Otherwise, there is no way to restrict the use of a public database link selectively (that is, any local user can connect to the remote database specified by the public database link).

Database Links and Security

In a distributed system, application developers and individual users are often allowed to create private database links. However, you must account for the extra security responsibilities required in a distributed system. See

for more information on security issues to consider when implementing a distributed system.

Database Links and Connection Qualifiers

Connection qualifiers provide a way to have several database links of the same type (for example, public) that point to the same remote database, yet establish those connections using different communications pathways.

A connection qualifier is a method of aliasing a database link to a particular communication pathway (or instance in the case of the Oracle Parallel Server). The connection qualifier is an identifying string appended to the database link name It is preceded by an at sign (@) (for example, emp.scott@HQ.ACME.COM@DBMS1).

For example, you have a database that is connected to the HQ.ACME.COM database DBS2 by an ethernet link and by a slower modem link. You want to access the DBS2 by both communication links allowing higher priority applications to use the faster ethernet link. You could define the following database links:

CREATE PUBLIC DATABASE LINK hq.acme.com@ethernet

    USING 'ethernet_to_hq.acme.com';

CREATE PUBLIC DATABASE LINK hq.acme.com@modem

    USING 'modem_to_hq.acme.com';

Note that in the above examples, the connection qualifiers (@ethernet, @modem) are appended to the database link name. The connection qualifier does not necessarily specify how the connection is to be established; this information is specified by the USING clause.

Based on the connection qualifiers specified above, the following statement would use the ethernet connection to HQ.ACME.COM:

SELECT * FROM scott.emp@hq.acme.com@ethernet

Connection qualifiers can also be defined to use different instances at a node where the remote database is managed by the Oracle Parallel Server.

Additional Information: For more information about database links and connection qualifiers, see the Oracle7 Server SQL Reference and your operating system-specific SQL*Net documentation. Oracle Names can also be used to define database links (except those in a replicated environment). See the Oracle Names Administrator's Guide for more information.

Oracle Names

Oracle Names is a distributed name service that resolves database service names and database links to network addresses, and makes them available to all clients in the network. When Oracle Names servers are used, it is no longer necessary to update every TNSNAMES.ORA file on every client whenever a change is made to an existing server or a new server is added to the network. Oracle Names is configured through Oracle Network Manager, so changes to an environment only need to be made at a single point for them to be available to all clients and servers.

The advantages of using Oracle Names servers are:

Network names and addresses can be stored in a central location on a database server, instead of being stored in TNSNAMES.ORA name lookup files on each client and server. Changes only need to be made using one utility--Network Manager--by administrators, instead of having to update name lookup files on hundreds or thousands of clients.

Oracle Names provides further location transparency by storing global database links, which eliminate the need to define and store them in local data dictionaries. Public and private database links will still be resolved properly. However, when using Oracle Names servers, they are no longer necessary.

Multiple Oracle Names servers can be distributed throughout an administrative region for constant coverage. If one Names server should temporarily fail, another Names server in that region can answer name requests.

For more information on Oracle Names, see the Oracle Names Administrator's Guide.

Automatic Creation of Global Database Links with Network Manager

When you define a network that includes Oracle Names, Network Manager automatically creates a global database link to every database server you define from every other database server in the network. These database links do not reside in the data dictionary, but in the network definition to which the Names servers refer. The default database links created do not initially include a CONNECT TO clause (that is, a username and password), so users reach the linked database using the same usernames and passwords as they use to reach the first database.

SQL> SELECT * FROM EMP@OHIO, DEPT@NY_FIN;

Explicitly Defined Database Links

You can edit global database links to include a username and password using Network Manager. When you edit a database, you can specify a single default username and password for the database link. See the Oracle Network Manager Administrator's Guide for details on how to edit global database links.

Connection Qualifiers

You can also define connection qualifiers to global database links through Network Manager. Connection qualifiers provide a way to create more than one link to a given database. These alternate links are a useful way to access different accounts on the same database with different sets of access privileges. The alternate link created by a connection qualifier must include a reference to a database by its global database name (or service name). See the Oracle Network Manager Administrator's Guide for details on how to create connection qualifiers using Network Manager.

**How SQL*Net Resolves Service Names**

When a user types in a service name, SQL*Net resolves it to an address using a variety of mechanisms in the following order:

First, SQL*net looks for the local TNSNAMES.ORA file for the user.

SQL*Net then searches the global TNSNAMES.ORA file.

SQL*Net then looks for the Oracle Names server address in the SQLNET.ORA file and contacts the Oracle Names server for resolution of the service name. Multiple Oracle Names server addresses can be stored in SQLNET.ORA. These addresses will be tried, in order, if the client is unable to connect to the first Oracle Names server.

If all attempts to resolve the name fail, Oracle issues the error message ORA-12154 TNS: could not resolve database name.

How Oracle Names Resolves Service Names

Service names (also called global database names) are translated to addresses in SQL*Net using the following method:

If the name is fully-qualified, in which case it contains the service name and the domain name, the given name is used. If not, SQL*Net will attach the domain suffix specified in the client profile. The domain suffix is stored in the SQLNET.ORA file under the entry NAMES.DEFAULT_DOMAIN.

SQL*Net then looks for the name in the client's TNSNAMES.ORA file, the search path of which is described in the previous section "How SQL*Net Resolves Service Names." If the name is still not resolved to an address, SQL*Net will then contact Oracle Names to resolve the address. In general, the use of Oracle Names replaces TNSNAMES.ORA as the way to map names to addresses.

Consider the following example:

SQLPLUS scott/tiger@hr

will connect to the database HR.US.ACME.COM if the client profile contains a default domain of US.ACME.COM.

SQLPLUS scott/tiger@hr.us.acme.com

is fully qualified and properly identifies the database HR.US.ACME.COM.

How Oracle Names Resolves Database Links

Similarly, for database links, the database looks at any defined private or public database link definitions and if not fully-qualified, the database domain (the GLOBAL_NAME minus the part preceding the first dot) is tacked on the database name of the link. If no USING clause is specified in the private or public database link definitions, and the database's client profile specifies one or more Oracle Names servers, these servers are called to resolve the database link name.

SQL*Net then receives either the database link's USING clause or the information returned by the Oracle Names server. If the USING clause contains a name, the name resolution process described above is then used to get the address. If the USING clause contains an address, the database link definition returned by Oracle Names is passed to SQL*Net, and name resolution is bypassed because an address has been directly provided.

Note: Though an address (SQL*Net connect descriptor) could conceivably be specified in the USING clause, a global database name is typically specified.

Consider the following example on the database MFG.US.ACME.COM:

A public database link HR@FIN exists and a user performs:

SELECT * FROM EMP@HR@FIN

The database will translate the database link name to HR.US.ACME.COM@FIN and call Oracle Names for link resolution because no USING clause was specified on the created link.

See Understanding SQL*Net and the Oracle Names Administrator's Guide for more information on service name and database link resolution.

Using a SELECT Statement across a Database Link

When you issue a select of a table across a database link, you acquire a transaction lock and a slot in the transaction table for the rollback segment for the local database. The lock can be released only by a commit or rollback.

private database link	Created on behalf of a specific user. A private database link can be used when the owner of the link specifies a global object name in a SQL statement, or in the definition of the owner's views or procedures.
public database link	Created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
global database link	Created and managed by a global naming service such as Oracle Names. A global database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.