Oracle | DBMS_CONNECTION

Oracle DBMS_CONNECTION_POOL Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Database resident connection pooling.

AUTHID DEFINER

Dependencies

CDB_CPOOL_INFO	GV_$CPOOL_CC_INFO	V_$CPOOL_CC_INFO
CPOOL$	GV_$CPOOL_CC_STATS	V_$CPOOL_CC_STATS
DBA_CPOOL_INFO	GV_$CPOOL_CONN_INFO	V_$CPOOL_CONN_INFO
DBMS_CONNECTION_POOL_LIB	GV_$CPOOL_STATS	V_$CPOOL_STATS

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-56500	Connection pool not found
ORA-56501	Connection pool startup failed
ORA-56504	Invalid connection pool configuration parameter name
ORA-56505	Invalid connection pool configuration parameter value
ORA-56506	Connection pool shutdown failed
ORA-56507	Connection pool alter configuration failed
ORA-56620	DRCP: removing the pool <string> failed because pool doesn't exist

First Available 11.1

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/prvtkppb.plb
{ORACLE_HOME}/rdbms/admin/prvtkpps.plb

Subprograms

ADD_POOL	REMOVE_POOL	STOP_POOL
ALTER_PARAM	RESTORE_DEFAULTS	Connection Demo
CONFIGURE_POOL	START_POOL

ADD_POOL (new 23ai)

Creates a new database resident connection pool dbms_connection_pool.add_pool pool_name IN VARCHAR2, minsize IN BINARY_INTEGER, max_size IN BINARY_INTEGER, incrsize IN BINARY_INTEGER, session_cached_cursors IN BINARY_INTEGER, inactivity_timeout IN BINARY_INTEGER, max_think_time IN BINARY_INTEGER, max_use_session IN BINARY_INTEGER, max_lifetime_session IN BINARY_INTEGER, max_txn_think_time IN BINARY_INTEGER);

TBD

ALTER_PARAM

Alters a specific configuration parameter as a standalone unit and does not affect other parameters dbms_connection_pool.alter_param( pool_name IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL', param_name IN VARCHAR2, param_value IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      set linesize 121

      col connection_pool format a30

      

      SELECT connection_pool, max_lifetime_session

      FROM dba_cpool_info;

      

      CONNECTION_POOL               MAX_LIFETIME_SESSION

      ---------------------------- ---------------------

      SYS_DEFAULT_CONNECTION_POOL                  86400

      

      exec dbms_connection_pool.alter_param(param_name=>'MAX_LIFETIME_SESSION', '43200');

      

      PL/SQL procedure successfully completed.

      

      SELECT connection_pool, max_lifetime_session

      FROM dba_cpool_info;

      

      exec dbms_connection_pool.restore_defaults;

      

      PL/SQL procedure successfully completed.

      

      SELECT connection_pool, max_lifetime_session

      FROM dba_cpool_info;

      

      CONNECTION_POOL               MAX_LIFETIME_SESSION

      ---------------------------- ---------------------

      SYS_DEFAULT_CONNECTION_POOL                  43200

CONFIGURE_POOL

Configures the pool with advanced options dbms_connection_pool.configure_pool( pool_name IN VARCHAR2 DEFAULT SYS_DEFAULT_CONNECTION_POOL, minsize IN BINARY_INTEGER DEFAULT 4, maxsize IN BINARY_INTEGER DEFAULT 40, incrsize IN BINARY_INTEGER DEFAULT 2, session_cached_cursors IN BINARY_INTEGER DEFAULT 20, inactivity_timeout IN BINARY_INTEGER DEFAULT 300, max_think_time IN BINARY_INTEGER DEFAULT 120, max_use_session IN BINARY_INTEGER DEFAULT 500000, max_lifetime_session IN BINARY_INTEGER DEFAULT 86400 max_txn_think_time IN BINARY_INTEGER);

conn sys@pdbdev as sysdba

      

      set linesize 121

      col connection_pool format a30

      

      SELECT connection_pool, maxsize

      FROM dba_cpool_info;

      

      CONNECTION_POOL               MAXSIZE

      ---------------------------- --------

      SYS_DEFAULT_CONNECTION_POOL        40

      

      exec dbms_connection_pool.configure_pool('SYS_DEFAULT_CONNECTION_POOL', maxsize=>50);

      

      PL/SQL procedure successfully completed.

      
      

      SELECT connection_pool, maxsize

      FROM dba_cpool_info;

      

      exec dbms_connection_pool.restore_defaults;

      

      PL/SQL procedure successfully completed.

      

      SELECT connection_pool, maxsize

      FROM dba_cpool_info;

      

      CONNECTION_POOL               
      MAXSIZE

      ---------------------------- --------

      SYS_DEFAULT_CONNECTION_POOL        50

REMOVE_POOL (new 23ai)

Removes a database resident connection pool dbms_connection_pool.remove_pool(pool_name IN VARCHAR2);

exec dbms_connection_pool.remove_pool('ZZYZX');

      

      PL/SQL procedure successfully completed.

RESTORE_DEFAULTS

Restores the pool to default settings dbms_connection_pool.restore_defaults( pool_name IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL');

exec dbms_connection_pool.restore_defaults;

      

      PL/SQL procedure successfully completed.

START_POOL

Starts the pool for operations. It is only after this call that the pool could be used by connection classes for creating sessions dbms_connection_pool.start_pool( pool_name IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL');

exec dbms_connection_pool.start_pool;

      

      PL/SQL procedure successfully completed.

STOP_POOL

Stops the pool and makes it unavailable for the registered connection classes dbms_connection_pool.stop_pool( pool_name IN VARCHAR2 DEFAULT 'SYS_DEFAULT_CONNECTION_POOL', draintime IN BINARY_INTEGER);

exec dbms_connection_pool.stop_pool;

      

      PL/SQL procedure successfully completed.

Connection Demo

A modification to the default TNSNAMES configuration must be made to take advantage of connection pooling -- client syntax (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=prod-server)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=ods)(SERVER=pooled)))

-- EZ connect syntax

      

      sqlplus uwclass@prod-server:1521/ods:POOLED