Maximum active sessions exceeded

Here is a new script and example output:

https://www.bobbydurrettdba.com/uploads/maxactive.zip

Use this query if you are using resource manager and have set a maximum number of active sessions.  This SQL reports the instance number and resource consumer group that have exceeded the maximum.

Here is the text of the query:

select
act.inst_id,act.resource_consumer_group,act.num_active,
pd.active_sess_pool_p1 max_active
from
(select inst_id,resource_consumer_group,count(*) num_active
from gv$session 
where status='ACTIVE' and
type='USER' and
program not like '%(P%)'
group by inst_id,resource_consumer_group) act,
DBA_RSRC_PLAN_DIRECTIVES pd,
gv$rsrc_plan pl
where
pd.plan=pl.name and
act.inst_id=pl.inst_id and
pl.is_top_plan='TRUE' and
act.resource_consumer_group=pd.group_or_subplan and
act.num_active > pd.active_sess_pool_p1;

– Bobby

P.S.  Modified query on 4/3/2012 – wasn’t excluding parallel query slaves correctly