Title: Listing Memory Used By All Sessions

Author's: Jay Caviness, a Senior Oracle DBA for Timebridge Technologies in
Englewood, Colorado, and was originally written by Frank Bommarito.

This script provides a listing of the uga and pga memory for every session in the current instance from smallest to largest.

column name  format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg   format 99,999,999 heading 'Avg Bytes'
column min   format 99,999,999 heading 'Min Bytes'
column max   format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'


compute sum of minmem on report
compute sum of maxmem on report
break on report

select se.sid,n.name,
       max(se.value) maxmem
from v$sesstat se,
     v$statname n
where n.statistic# = se.statistic#
and   n.name in ('session pga memory','session pga memory max',
                 'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/