General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
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
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