SGA Usage Report

SGA Usage Report:
break on report
compute sum of mb on report
compute sum of inuse on report
set pagesize 50
col mb format 999,999
col inuse format 999,999
select name,
       round(sum(mb),1) mb,
       round(sum(inuse),1) inuse
  from (select case when name = 'buffer_cache'
                    then 'db_cache_size'
                    when name = 'log_buffer'
                    then 'log_buffer'
                    else pool
                end name,
                bytes/1024/1024 mb,
                case when name <> 'free memory'
                     then bytes/1024/1024
                end inuse
           from v$sgastat
       )
 group by name
 order by mb desc
/
exit
/

Sample Output:
NAME                MB    INUSE

------------- -------- --------
db_cache_size   85,504   85,504
shared pool      6,144    3,879
streams pool       512      256
large pool         256        1
java pool          256
log_buffer          98       98
                     2        2
              -------- --------
sum             92,773   89,741