show sessions idle for 10, 15, 30, 60, 90, 120 mins and their pga usage.

idle session pga usage.sql

set linesize 200
 select b.username
, count(*) "sessions"
, round(sum(a.pga_alloc_mem)/1024/1024) "all sessions mb"
, round(avg(b.last_call_et/60)) "avg idle time"
, round(avg(a.pga_alloc_mem)/1024/1024) "avg mb"
, sum(case when b.last_call_et >=600 then 1 else 0 end) "idle 10"
, round(sum(case when b.last_call_et >=600 then a.pga_alloc_mem else 0 end)/1024/1024) "idle 10 MB"
, sum(case when b.last_call_et >=900 then 1 else 0 end) "idle 15"
, round(sum(case when b.last_call_et >=900 then a.pga_alloc_mem else 0 end)/1024/1024) "idle 15 MB"
, sum(case when b.last_call_et >=1800 then 1 else 0 end) "idle 30"
, round(sum(case when b.last_call_et >=1800 then a.pga_alloc_mem else 0 end)/1024/1024) "idle 30 MB"
, sum(case when b.last_call_et >=3600 then 1 else 0 end) "idle 60"
, round(sum(case when b.last_call_et >=3600 then a.pga_alloc_mem else 0 end)/1024/1024) "idle 60 MB"
, sum(case when b.last_call_et >=5400then 1 else 0 end) "idle 90"
, round(sum(case when b.last_call_et >=5400 then a.pga_alloc_mem else 0 end)/1024/1024) "idle 90 MB"
, sum(case when b.last_call_et >=7200 then 1 else 0 end) "idle 120"
, round(sum(case when b.last_call_et >=7200 then a.pga_alloc_mem else 0 end)/1024/1024) "idle 120 MB"
from v$process a
, v$session b
where a.addr=b.paddr
and b.type='USER' and b.status = 'INACTIVE'
group by b.username order by username;