Cheeky Cockatoo

set pagesize 500 feedback off
Ttitle left '==== Session Memory Report ==== ' skip 2
column sess_mem format 999,999,999 heading 'Session Memory'
compute avg of sess_mem on report
compute sum label 'Total Session Memory: ' of sess_mem on report
break on report skip 1 on report skip 1

select s.sid, e.username, value sess_mem , t.sql_text
from v$sesstat s, v$statname n, v$session e, v$sqltext T
where s.statistic# = n.statistic#
and n.name= 'session uga memory'
and s.sid = e.SID 
and e.sql_address=t.address
and e.sql_hash_value=t.hash_value
and t.piece = 0
order by sess_mem;