DBMS CONNECTION POOL

http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/manproc004.htm#ADMIN12350

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10577/d_connection_pool.htm#ARPLS65617

Administrating Database Resident Connection Pools

/******* Oracle Client Upgrade　: oracle 11g client install

ERROR:
ORA-56606: DRCP: Client version does not support the feature
56606, 0000, "DRCP: Client version does not support the feature"
// *Cause: The client version is lower than 11g.
// *Action: Upgrade to a higher client version or turn off (SERVER=POOLED)
//          in the connect string.
********/

To implement database resident connection pooling, it must first be enabled on the system by a user with SYSDBA privileges. See Example 3-12 for steps necessary to initiate and maintain a database resident connection pool.

Example 3-12 How to Administer the Database Resident Connection Pools

A user with SYSDBA privileges must perform the next steps.

1. Connect to the database.

   SQLPLUS / AS SYSDBA
   

2. [Optional] Configure the parameters of the database resident connection pool. The default values of a pool are set in the following way:

   EXECUTE DBMS_CONNECTION_POOL.CONFIGURE_POOL('SYS_DEFAULT_CONNECTION_POOL',MINSIZE=>10,MAXSIZE=>200);

3. [Optional] Alter specific parameters of the database resident connection pool without affecting other parameters.

   DBMS_CONNECTION_POOL.ALTER_PARAM(  'SYS_DEFAULT_CONNECTION_POOL', 
                                      'INACTIVITY_TIMEOUT', 
                                       10);
   

4. Start the connection pool. After this step, the connection pool is available to all qualified clients.

   DBMS_CONNECTION_POOL.START_POOL( 'SYS_DEFAULT_CONNECTION_POOL');
   

5. [Optional] Change the parameters of the database resident connection pool.

   DBMS_CONNECTION_POOL.ALTER_PARAM( 'SYS_DEFAULT_CONNECTION_POOL', 
                                     'MAXSIZE', 
                                      20);
   

6. [Optional] The configuration of the connection pool can be reset to default values.

   DBMS_CONNECTION_POOL.RESTORE_DEFAULTS ( 'SYS_DEFAULT_CONNECTION_POOL');
   

7. Stop the pool. Note that pool information is persistent: stopping the pool does not destroy the pool name and configuration parameters.

   DBMS_CONNECTION_POOL.STOP_POOL();
   

• Routing Client Connection Requests to the Connection Pool

In the client application, the connect string must specify the connect type as POOLED.

The following example shows an easy connect string that enables clients to connect to a database resident connection pool:

examplehost.company.com:1521/books.company.com:POOLED

The following example shows a TNS connect descriptor that enables clients to connect to a database resident connection pool:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myhost)
       (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales)
       (SERVER=POOLED)))

• Data Dictionary Views for Database Resident Connection Pooling

View	Description
DBA_CPOOL_INFO	Contains information about the connection pool such as the pool status, the maximum and minimum number of connections, and timeout for idle sessions.
V$CPOOL_CONN_INFO	Contains information about each connection to the connection broker.
V$CPOOL_STATS	Contains pool statistics such as the number of session requests, number of times a session that matches the request was found in the pool, and total wait time for a session request.
V$CPOOL_CC_STATS	Contains connection class level statistics for the pool.