|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
Every application that references a remote server using a standard database link establishes a connection between the local database and the remote database. Many users running applications simultaneously can cause a high number of connections between the local and remote databases.
This section contains the following topics:
See Also:"What Are Shared Database Links?" for a conceptual overview of shared database links
Look carefully at your application and shared server configuration to determine whether to use shared links. A simple guideline is to use shared database links when the number of users accessing a database link is expected to be much larger than the number of server processes in the local database.
The following table illustrates three possible configurations involving database links:
|Link Type||Server Mode||Consequences|
|Nonshared||Dedicated/shared server||If your application uses a standard public database link, and 100 users simultaneously require a connection, then 100 direct network connections to the remote database are required.|
|Shared||Shared server||If 10 shared server processes exist in the local shared server mode database, then 100 users that use the same database link require 10 or fewer network connections to the remote server. Each local shared server process may only need one connection to the remote server.|
|Shared||Dedicated||If 10 clients connect to a local dedicated server, and each client has 10 sessions on the same connection (thus establishing 100 sessions overall), and each session references the same remote database, then only 10 connections are needed. With a nonshared database link, 100 connections are needed.|
Shared database links are not useful in all situations. Assume that only one user accesses the remote server. If this user defines a shared database link and 10 shared server processes exist in the local database, then this user can require up to 10 network connections to the remote server. Because the user can use each shared server process, each process can establish a connection to the remote server.
Clearly, a nonshared database link is preferable in this situation because it requires only one network connection. Shared database links lead to more network connections in single-user scenarios, so use shared links only when many users need to use the same link. Typically, shared links are used for public database links, but can also be used for private database links when many clients access the same local schema (and therefore the same private database link).
Note:In a multitiered environment, there is a restriction that if you use a shared database link to connect to a remote database, then that remote database cannot link to another database with a database link that cannot be migrated. That link must use a shared server, or it must be another shared database link.
CREATE SHARED DATABASE LINK dblink_name [CONNECT TO username IDENTIFIED BY password]|[CONNECT TO CURRENT_USER] AUTHENTICATED BY schema_name IDENTIFIED BY password [USING 'service_name'];
Whenever you use the keyword
SHARED, the clause
AUTHENTICATED BY is required. The schema specified in the
AUTHENTICATED BY clause must exist in the remote database and must be granted at least the
CREATE SESSION privilege. The credentials of this schema can be considered the authentication method between the local database and the remote database. These credentials are required to protect the remote shared server processes from clients that masquerade as a database link user and attempt to gain unauthorized access to information.
After a connection is made with a shared database link, operations on the remote database proceed with the privileges of the
TO user or
CURRENT_USER, not the
The following example creates a fixed user, shared link to database
sales, connecting as
scott and authenticated as
CREATE SHARED DATABASE LINK link2sales CONNECT TO scott IDENTIFIED BY password AUTHENTICATED BY linkuser IDENTIFIED BY ostrich USING 'sales';
See Also:Oracle Database SQL Language Reference for information about the
CREATE DATABASE LINKstatement
In the configuration illustrated in Figure 32-1, a shared server process in the local server owns a dedicated remote server process. The advantage is that a direct network transport exists between the local shared server and the remote dedicated server. A disadvantage is that extra back-end server processes are needed.
Note:The remote server can either be a shared server or dedicated server. There is a dedicated connection between the local and remote servers. When the remote server is a shared server, you can force a dedicated server connection by using the (
SERVER=DEDICATED) clause in the definition of the service name.
Figure 32-1 A Shared Database Link to Dedicated Server Processes
The configuration illustrated in Figure 32-2 uses shared server processes on the remote server. This configuration eliminates the need for more dedicated servers, but requires the connection to go through the dispatcher on the remote server. Note that both the local and the remote server must be configured as shared servers.
Figure 32-2 Shared Database Link to Shared Server
See Also:"Shared Server Processes" for information about the shared server option