Database Resident Connection Pool (DRCP) in Oracle Database 11g Release 1

The database resident connection pool (DRCP) reduces the resource requirements of applications that currently don't support connection pooling, either because it is not supported by the application infrastructure, or it has not been implemented. The pool is managed using the DBMS_CONNECTION_POOL package. Although the package appears to support multiple connection pools, the document states that it currently only supports the default pool name (SYS_DEFAULT_CONNECTION_POOL).

The DRCP is started and stopped using the START_POOL and STOP_POOL procedures respectively.

SQL> EXEC DBMS_CONNECTION_POOL.start_pool;

PL/SQL procedure successfully completed.

SQL>

The ALTER_PARAM procedure allows you to configure an individual pool parameter, while the CONFIGURE_POOL allows you to configure all pool parameters in one call. The default settings are restored using the RESTORE_DEFAULTS procedure. The pool parameters that are currently supported are listed below.

PROCEDURE CONFIGURE_POOL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 POOL_NAME                      VARCHAR2                IN     DEFAULT
 MINSIZE                        BINARY_INTEGER          IN     DEFAULT
 MAXSIZE                        BINARY_INTEGER          IN     DEFAULT
 INCRSIZE                       BINARY_INTEGER          IN     DEFAULT
 SESSION_CACHED_CURSORS         BINARY_INTEGER          IN     DEFAULT
 INACTIVITY_TIMEOUT             BINARY_INTEGER          IN     DEFAULT
 MAX_THINK_TIME                 BINARY_INTEGER          IN     DEFAULT
 MAX_USE_SESSION                BINARY_INTEGER          IN     DEFAULT
 MAX_LIFETIME_SESSION           BINARY_INTEGER          IN     DEFAULT

With the pool started, a connection can be made using the easy connect method.

SQL> CONNECT test/test@ole45.localdomain:1521/DB11G.WORLD:POOLED
Connected.
SQL>

Set the server type to POOLED in the tnsnames.ora file to allow connection pooling for a connection string.

DB11G_POOL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ole45.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
      (SERVER = POOLED)
    )
  )

Pooled connections are then possible using the amended connection string.

SQL> CONNECT test/test@DB11G_POOL
Connected.
SQL>

If a session remains idle for longer than the inactivity_timeout it is killed to free up space in the connection pool.

SQL> SELECT sydate FROM dual;
select sydate from dual
                      *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7902
Session ID: 170 Serial number: 20


SQL>

Attempting to connect to the pool when the pool is stopped results in the following error.

SQL> conn / as sysdba
Connected.
SQL> EXEC DBMS_CONNECTION_POOL.stop_pool;

PL/SQL procedure successfully completed.

SQL> CONNECT test/test@DB11G_POOL
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server


Warning: You are no longer connected to ORACLE.
SQL>

Information about connection pooling is displayed using the following views:

For more information see:

Hope this helps. Regards Tim...

Back to the Top.