Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

Part Number B28419-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

32 DBMS_CONNECTION_POOL

The DBMS_CONNECTION_POOL package provides an interface to manage Database Resident Connection Pool.

See Also:

Oracle Database Concepts for more information on "Database Resident Connection Pooling"

This chapter contains the following topic:


Summary of DBMS_CONNECTION_POOL Subprograms

Table 32-1 DBMS_CONNECTION_POOL Package Subprograms

Subprogram Description
ALTER_PARAM Procedure
Alters a specific configuration parameter as a standalone unit and does not affect other parameters
CONFIGURE_POOL Procedure
Configures the pool with advanced options
START_POOL Procedure
Starts the pool for operations. It is only after this call that the pool could be used by connection clients for creating sessions
STOP_POOL Procedure
Stops the pool and makes it unavailable for the registered connection clients
RESTORE_DEFAULTS Procedure
Restores the pool to default settings


ALTER_PARAM Procedure

This procedure alters a specific configuration parameter as a standalone unit and does not affect other parameters.

Syntax

DBMS_CONNECTION_POOL.ALTER_PARAM (
   pool_name     IN  VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL',   param_name    IN  VARCHAR2,   param_value   IN  VARCHAR2);

Parameters

Table 32-2 ALTER_PARAM Procedure Parameters

Parameter Description
pool_name Pool to be configured. Currently only the default pool name is supported.
param_name Any parameter name from CONFIGURE_POOL
param_value Parameter value for param_name.

Exceptions

Table 32-3 ALTER_PARAM Procedure Exceptions

Exception Description
ORA-56500 Connection pool not found
ORA-56504 Invalid connection pool configuration parameter name
ORA-56505 Invalid connection pool configuration parameter value
ORA-56507 Connection pool alter configuration failed

Examples

DBMS_CONNECTION_POOL.ALTER_PARAM(
   'SYS_DEFAULT_CONNECTION_POOL', 'MAX_LIFETIME_SESSION', '120'); 

CONFIGURE_POOL Procedure

This procedure configures the pool with advanced options.

Syntax

DBMS_CONNECTION_POOL.CONFIGURE_POOL (
   pool_name                IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL',
   minsize                  IN NUMBER   DEFAULT 4,
   maxsize                  IN NUMBER   DEFAULT 40,
   incrsize                 IN NUMBER   DEFAULT 2,
   session_cached_cursors   IN NUMBER   DEFAULT 20,
   inactivity_timeout       IN NUMBER   DEFAULT 300,
   max_think_time           IN NUMBER   DEFAULT 120,
   max_use_session          IN NUMBER   DEFAULT 500000,
   max_lifetime_session     IN NUMBER   DEFAULT 86400);

Parameters

Table 32-4 CONFIGURE_POOL Procedure Parameters

Parameter Description
pool_name Pool to be configured. Currently only the default pool name is supported.
minsize Minimum number of pooled servers in the pool
maxsize Maximum allowed pooled servers in the pool
incrsize Pool would increment by this number of pooled server when pooled server are unavailable at application request time
session_cached_cursors Turn on SESSION_CACHED_CURSORS for all connections in the pool. This is an existing init.ora parameter
inactivity_timeout TTL (Time to live) for an idle session in the pool. This parameter helps to shrink the pool when it is not used to its maximum capacity. If a connection remains in the pool idle for this time, it is killed.
max_think_time Maximum time of inactivity by the client after getting a session from the pool. If the client does not issue a database call after grabbing a server from the pool, the client will be forced to relinquish control of the pooled server and will get an ORA-xxxxx error. The freed up server may or may not be returned to the pool.
max_use_session Maximum number of times a connection can be taken and released to the pool
max_lifetime_session TTL (Time to live) for a pooled session

Exceptions

Table 32-5 CONFIGURE_POOL Procedure Exceptions

Exception Description
ORA-56500 Connection pool not found
ORA-56507 Connection pool alter configuration failed

Usage Notes


START_POOL Procedure

This procedure starts the pool for operations. It is only after this call that the pool could be used by connection classes for creating sessions.

Syntax

DBMS_CONNECTION_POOL.START_POOL (
   pool_name  IN  VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL');

Parameters

Table 32-6 START_POOL Procedure Parameters

Parameter Description
pool_name Pool to be started. Currently only the default pool name is supported.

Exceptions

Table 32-7 START_POOL Procedure Exceptions

Exception Description
ORA-56500 Connection pool not found
ORA-56501 Connection pool startup failed

Usage Notes

If the instance is restarted (shutdown followed by startup), the pool is automatically started.


STOP_POOL Procedure

This procedure stops the pool and makes it unavailable for the registered connection classes.

Syntax

DBMS_CONNECTION_POOL.STOP_POOL (
   pool_name   IN   VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL');

Parameters

Table 32-8 STOP_POOL Procedure Parameters

Parameter Description
pool_name Pool to be stopped. Currently only the default pool name is supported.

Exceptions

Table 32-9 STOP_POOL Procedure Exceptions

Exception Description
ORA-56500 Connection pool not found
ORA-56506 Connection pool shutdown failed

Usage Notes

This stops the pool and takes it offline. This does not destroy the persistent data (such as, the pool name and configuration parameters) associated with the pool.


RESTORE_DEFAULTS Procedure

This procedure restores the pool to default settings.

Syntax

DBMS_CONNECTION_POOL.RESTORE_DEFAULTS (
   pool_name   IN  VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL');

Parameters

Table 32-10 RESTORE_DEFAULTS Procedure Parameters

Parameter Description
pool_name Pool to be restored. Currently only the default pool name is supported.

Exceptions

Table 32-11 RESTORE_DEFAULTS Procedure Exceptions

Exception Description
ORA-56500 Connection pool not found
ORA-56507 Connection pool alter configuration failed

Usage Notes

If the instance is restarted (shutdown followed by startup), the pool is automatically started.