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 theCREATE
DATABASE LINK
statement. - Dropping Database Links
You can drop a database link with theDROP 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 parameterOPEN_LINKS
.
Parent topic: Using the Oracle Database Gateway for DRDA
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 theCONNECT 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.
Parent topic: Processing a Database Link
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 linksParent topic: Processing a Database Link
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.
Parent topic: Processing a Database Link
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.
Parent topic: Processing a Database Link