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
