3.2 Processing a Database Link

The database and application administrators of a distributed database system are responsible for managing the database links that define paths to the Oracle Database Gateway for DRDA.

The tasks are as follows:

• Creating Database Links
  To create a database link and define a path to a remote database, use the CREATE DATABASE LINK statement.
• Dropping Database Links
  You can drop a database link with the DROP DATABASE LINK statement.
• Examining Available Database Links
  The data dictionary of each database stores the definitions of all the database links in that database.
• Limiting the Number of Active Database Links
  You can limit the number of connections from a user process to remote databases with the parameter OPEN_LINKS.

3.2.1 Creating Database Links

To create a database link and define a path to a remote database, use the CREATE DATABASE LINK statement.

The CONNECT TO clause specifies the remote user ID and password to use when creating a session in the remote database. The USING clause points to a tnsnames.ora connect descriptor.

Note:

If you do not specify a user ID and a password in the CONNECT TO clause, then the Oracle database user ID and password are used.

See Also:

Refer to Chapter 15, "Security Considerations" in Oracle Database Gateway Installation and Configuration Guide for IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), Oracle Solaris on x86-64 (64-Bit) and HP-UX Itanium or Oracle Database Gateway Installation and Configuration Guide for Microsoft Windows for details.

The following example creates a database link to access information in the DRDA server:

CREATE PUBLIC DATABASE LINK dblink 
CONNECT TO userid IDENTIFIED BY password  
USING 'tns_name_entry';

where:

dblink is the complete database link name.

user id is the user ID used to establish a session in the remote database. This user ID must be a valid DRDA server user ID. It must be authorized to any table or file on the DRDA server that is referenced in the SQL commands. Length restrictions on user IDs are dependent on the authorization system used by the DRDA server. In many cases this limit is eight characters, but in other cases, it may be longer. See DB2 platform documentation for limitations.

password is the password used to establish a session in the remote database. This password must be a valid DRDA server password. Length restrictions on passwords are dependent on the authorization system used by the DRDA server. In many cases this limit is eight characters, but in other cases, it may be longer. See DB2 platform documentation for limitations.

tns_name_entry specifies the Oracle Net connect descriptor used to identify the gateway.

Guidelines for Database Links

Database links are active for the duration of a gateway session. If you want to close a database link during a session, then use the ALTER SESSION CLOSE DATABASE LINK dblink statement.

3.2.2 Dropping Database Links

You can drop a database link with the DROP DATABASE LINK statement.

For example, to drop the public database link named DBLINK, use the statement:

DROP PUBLIC DATABASE LINK dblink;

Note:

A database link should not be dropped if it is required to resolve an in-doubt distributed transaction. Refer to Oracle Database Administrator's Guide for additional information about dropping database links.

See Also:

Oracle Database SQL Language Reference for additional information about dropping database links

3.2.3 Examining Available Database Links

The data dictionary of each database stores the definitions of all the database links in that database.

The USER_DB_LINKS data dictionary view shows the privately defined database links, that is, those accessible to the current Oracle user. The ALL_DB_LINKS data dictionary views show all accessible (public and private) database links.

3.2.4 Limiting the Number of Active Database Links

You can limit the number of connections from a user process to remote databases with the parameter OPEN_LINKS.

This parameter controls the number of remote connections that any single user process can concurrently use with a single SQL statement. Refer to Oracle Database Administrator’s Guide for additional information about limiting the number of active database links.