Oracle | DBMS_CONNECTION

Oracle DBMS_CONNECTION_POOL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

First Available 11.1

Security Model Owned by SYS with no privileges granted

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

Subprograms

ALTER_PARAM	RESTORE_DEFAULTS	STOP_POOL
CONFIGURE_POOL	START_POOL	Connection Demo

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;

      

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

      

      SELECT connection_pool, max_lifetime_session

      FROM dba_cpool_info;

      

      exec dbms_connection_pool.restore_defaults;

      

      SELECT connection_pool, max_lifetime_session

      FROM dba_cpool_info;

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;

      

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

      

      SELECT connection_pool, maxsize

      FROM dba_cpool_info;

      

      exec dbms_connection_pool.restore_defaults;

      

      SELECT connection_pool, maxsize

      FROM dba_cpool_info;

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;

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;

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');

exec dbms_connection_pool.stop_pool;

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