Oracle DBMS_CONNECTION_POOL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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 pdborcl 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);
conn sys@pdborcl 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
Packages

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