SGA usage
SET LINESIZE 145
SET PAGESIZE 9999
SET FEEDBACK off
SET VERIFY off
COLUMN bytes FORMAT 999,999,999
COLUMN percent FORMAT 999.99999
break on report
compute sum of bytes on report
compute sum of percent on report
SELECT
a.name
, a.bytes
, a.bytes/(b.sum_bytes*100) Percent
FROM sys.v_$sgastat a
, (SELECT SUM(value)sum_bytes FROM sys.v_$sga) b
ORDER BY bytes DESC
/
SGA free pool
COLUMN pool HEADING "Pool"
COLUMN name HEADING "Name"
COLUMN sgasize HEADING "Allocated" FORMAT 999,999,999
COLUMN bytes HEADING "Free" FORMAT 999,999,999
SELECT
f.pool
, f.name
, s.sgasize
, f.bytes
, ROUND(f.bytes/s.sgasize*100, 2) "% Free"
FROM
(SELECT SUM(bytes) sgasize, pool FROM v$sgastat GROUP BY pool) s
, v$sgastat f
WHERE
f.name = 'free memory'
AND f.pool = s.pool
/