Oracle DBMS_CONNECTION_POOL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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
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.0.6
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('SYS_DEFAULT_CONNECTION_POOL', '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(new 12.2 parameter)
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

Related Topics
Built-in Functions
Built-in Packages
DBMS_PROCESS
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved