what is oracle active session pool? is it activated by default? what is the default maximum active session that there is per user?
The Active Session Pool allows a limit to be place on the number of sessions that can be active on your instance. It is a part of the Resource Manager. The number possible is defined according to your current resource plan. The default plan allows an unlimited number of sessions. It is activated if resource management is performed in the instance. To find out if this is so then check the
show parameter resource_manager_plan
If there is a value then it is activated. You may see something like
DEFAULT_PLAN as the
VALUE. You can then query
DBA_RSRC_PLAN_DIRECTIVES for the number of active sessions possible:
select GROUP_OR_SUBPLAN, ACTIVE_SESS_POOL_P1 from DBA_RSRC_PLAN_DIRECTIVES where PLAN = 'DEFAULT_PLAN';
You'll get a few rows back relating to the number of groups/subplans that are part of this plan. NULL will indicate unlimited. I prefer using Enterprise Manager to query and amend this. To find it in EM go to the Server tab then under the Resource Manager heading is Plans.
More information on the Resource Manager can be found here.