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 is now availble. If you haven't you downloaded already: Why?

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