Oracle7 Server Distributed Systems Volume I: Distributed Data

Controlling Connections Established by Database Links

When a global object name is referenced in a SQL statement or remote procedure call, database links establish a connection to a session in the remote database on behalf of the local user. The remote connection and session are only created if the connection has not already been established previously for the local user session.

The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application (or user) explicitly terminates them. Terminating remote connections established using database links is useful for disconnecting high cost connections (such as long distance phone connections) that are no longer required by the application.

The application developer or user can close (terminate) a remote connection and session using the ALTER SESSION command with the CLOSE DATABASE LINK parameter. For example, assume you issue the following query:

SELECT * FROM emp@sales;
COMMIT;

The following statement terminates the session in the remote database pointed to by the SALES database link:

ALTER SESSION CLOSE DATABASE LINK sales;

If partial global database names are specified, the local Oracle expands the name using the network domain of the local database.

Note: Before closing a database link, you must first close all cursors that use the link and then end your current transaction if it uses the link.

To close a database link connection in your user session, you must have the ALTER SESSION system privilege.