Oracle DBMS_CONNECTION_POOL
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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
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
What's New In 12cR2
What's New In 18cR3

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-2019 Daniel A. Morgan All Rights Reserved