| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/prvtkppb.plb |
| First Available |
11.1 |
| Dependencies |
| CPOOL$ |
DBA_CPOOL_INFO |
DBMS_CONNECTION_POOL_LIB |
| GV_$CPOOL_CC_INFO |
GV_$CPOOL_CC_STATS |
GV_$CPOOL_STATS |
|
| Exceptions |
| Error |
Description |
| 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 |
|
| Security Model |
Owned by SYS with no granted privileges |
| 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 / 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 / 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', max_size=50); |
| |
| 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; |
| |
| TNSNAMES.ORA |
| 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 |
