-- User Global Area (UGA) Memory - in dedicated server mode is part of PGA
-- In shared server mode it is a part of SGA

SELECT  'Total Memory For All Sessions = '||SUM(ss.value)  total_memory 
FROM v$sesstat ss,
    v$statname sn
WHERE ss.statistic# = sn.statistic#
AND   UPPER(sn.name) = 'SESSION UGA MEMORY'
;

SELECT  'Total Max. Memory For All Sessions = '||SUM(ss.value)  total_max_memory 
FROM v$sesstat ss,
    v$statname sn
WHERE ss.statistic# = sn.statistic#
AND   UPPER(sn.name) = 'SESSION UGA MEMORY MAX'
;


SELECT  sn.name, ss.value/1024/1024  memory_mb
FROM 
 v$session s,
 v$statname sn,
 v$sesstat ss
WHERE s.sid = ss.sid
AND   sn.statistic# = ss.statistic#
AND   UPPER(s.type) = 'USER'
AND   s.username IS NOT NULL
AND   UPPER(sn.name) IN
  ('SESSION PGA MEMORY', 'SESSION PGA MEMORY MAX'
   'SESSION UGA MEMORY', 'SESSION UGA MEMORY MAX')
;