Public and Private Database Links

Manually Creating Database Links

This section discusses how public and private links are created. For information on how global database links are defined, see "Creating Global Database Links in Network Manager" .

The syntax for creating public and private links is:

CREATE [PUBLIC] DATABASE LINK linkname 
[CONNECT TO username IDENTIFIED BY password] 
USING 'service_name' 

In this syntax:

[PUBLIC]	Specifies a database link available to all users with the CREATE SESSION privilege. If the PUBLIC option is omitted, a private link available only to the creator is created. Note that creating a public database link requires CREATE PUBLIC DATABASE LINK privilege.
linkname	Specifies the name of the database link. If the remote server is in the local server's domain, the link name does not need to include the domain name. However, if the server is in another domain, the link name must include the domain. (The domain is determined by DB_DOMAIN in the initialization parameter file).
CONNECT TO	Optionally specifies a single username and password for all users of the database link to share. If the clause is omitted, the Oracle username and password of the user account using the database link will connect to the remote database server.
username	Specifies a valid Oracle username on the remote database server.
password	Specifies the corresponding password of the username on the remote database server.
service_name	Specifies the service name defined in the TNSNAMES.ORA file or stored in Oracle Names associated with the connect descriptor for the desired database. If the remote server is in the local server's default domain, the service name does not need to include the domain name. However, if the server is in another domain, the service name must include the domain. (The default domain is determined by a parameter in the server's SQLNET.ORA file.

Before Oracle7, a database administrator could specify any linkname for a database link. However, with Oracle7 and later releases, a database link must have the same name as the global database name of the database. Remember that the service name is also the same as the global database name. Therefore, the linkname and service name are now the same. Although this may seem to make the USING clause redundant, it is still a necessary part of the syntax.

For example, the command for creating a public database link to a database that has the global database name ORCHID.HQ.ACME is as follows:

CREATE PUBLIC DATABASE LINK ORCHID.HQ.ACME
CONNECT TO scott IDENTIFIED BY tiger
USING 'ORCHID.HQ.ACME'

The following statement is the complete CREATE DATABASE LINK statement shown earlier. This example illustrates the creation of the SALES database link and the complete path that is specified for the link:

CREATE PUBLIC DATABASE LINK sales.division3.acme.com 

	CONNECT TO guest IDENTIFIED BY password 

	USING 'dbstring'; 

When a database link is created, a complete path (the remote account and the database string), a partial path (just the remote account or just the database string), or no path can be specified.

When a SQL statement references a global object name in the SALES database, the local Oracle node finds the corresponding SALES database link in the local database and attempts to establish a session in the remote database for the user GUEST/PASSWORD. The database string specified in the SALES database link definition (which is operating system and network dependent) is used to facilitate the remote connection.

Typically, it is the responsibility of each database administrator or application administrator to create the necessary database links to databases throughout the network. Database links are an implementation detail that should be completely transparent to applications and end-users of a database. It should appear to applications and users that a remote table is accessed by specifying the table's global object name, not by referencing an available database link. In fact, administrators should create location transparency for remote objects using views, synonyms, or procedures, so that applications do not explicitly reference remote data. Then, if the remote object is moved, only the synonym needs to be altered, not all applications.

Public Database Links with a Default Connection

CREATE PUBLIC DATABASE LINK NY_FIN.HQ.ACME
USING 'NY_FIN.HQ.ACME'

Figure 4 - 4. Public Database Link with Default Connection

Users connected to OHIO.SALES.ACME can use the NY_FIN.HQ.ACME database link to connect to NY_FIN.HQ.ACME with the same username and password they have on OHIO.SALES.ACME. To access the table on NY_FIN.HQ.ACME called EMP, any user could issue the SQL query:

SQL> SELECT * FROM EMP@NY_FIN.HQ.ACME; 

Note: If the target database were in the source database's default domain, the user would not need to include the domain in the link name or service name, or in the SELECT command.

This query would initiate a connection from OHIO to NY_FIN with the current username and password to log onto NY_FIN. The query would then be processed on NY_FIN. The data available to the current user from the table EMP would be returned to OHIO. Each user creates a separate connection to the server. Subsequent queries to that database link by that user would not require an additional logon.

Public Database Links with a Specific Connection

CREATE PUBLIC DATABASE LINK NY_FIN
CONNECT TO FINPUBLIC IDENTIFIED BY NOPASS
USING 'NY_FIN'

Figure 4 - 5. Public Database Link with Specific Connection

Any user connected to OHIO can use the NY_FIN database link to connect to NY_FIN with the common username/password of FINPUBLIC/NOPASS. To access the table in the FINPUBLIC account of NY_FIN called ALL_SALES, any user could issue the SQL query:

SQL> SELECT * FROM ALL_SALES@NY_FIN; 

This query initiates a connection from OHIO to NY_FIN to the common account FINPUBLIC. The query is processed on NY_FIN, and data from the table ALL_SALES are returned to OHIO.

Each user creates a separate connection to the common account on the server. Subsequent queries to that database link by that user would not require an additional logon.

Connection Qualifiers

A connection qualifier contains a qualifier name and, optionally, a username and password. To create a connection qualifier, use a statement similar to the following:

CREATE PUBLIC DATABASE LINK NY_FIN@PROFITS
CONNECT TO ACCOUNTS IDENTIFIED BY TAXES
USING 'NY_FIN'

To use the connection qualifier, you append the qualifier name to the service name of the database you want to access.

For example, the following SQL queries use three database links to the same database, using different connection qualifiers:

SELECT * FROM EMP@NY_FIN;

SELECT * FROM SCHEDULE@NY_FIN@PROFITS;

SELECT * FROM EMPSALARIES@NY_FIN@FIN;

In this example @PROFITS and @FIN are connection qualifiers.

Dropping a Database Link

DROP DATABASE LINK linkname; 

For example, to drop the database link NY_FIN, the command would be:

DROP DATABASE LINK NY_FIN; 

Examining Available Database Links

For example, assume that the local database's global name is MKTG.ACME.COM. Also assume that the following CREATE DATABASE LINK statements have been issued by the same user:

CREATE DATABASE LINK hq.acme.com

   CONNECT TO guest IDENTIFIED BY password

CREATE DATABASE LINK sales USING 'dbstring';

The following query lists all of the private database links contained in the schema associated with the current user issuing the query:

SELECT db_link, username, host

   FROM user_db_links;

For example, if the user that owns the previously created database links (HQ and SALES) issues the query above, results similar to those below are returned:

DB_LINK           USERNAME  HOST

----------------  --------  ----------

HQ.ACME.COM       GUEST

SALES.ACME.COM              dbstring

Notice that the USERNAME and HOST fields can be null if database link definitions do not indicate complete paths to the remote database.

Finding Available Database Links

Limiting the Number of Active Database Links