shared pool free memory

--
-- Free memory in shared pool and in reserved_pool separately, percent of free space current and min for reserved pool
--
select s.pool, round((s.bytes - p.free_space)/1024/1024, 3) fm, round((p.free_space)/1024/1024, 3) fm_reserved
, round(((s.bytes - p.free_space)/decode(sps.value, 0, NULL, sps.value))*100, 3) pct_free
, round(((sps_reserved.value - p.max_used_size)/sps_reserved.value)*100, 3) pct_free_reserved_min
from v$sgastat s, v$shared_pool_reserved p, v$parameter sps, v$parameter sps_reserved
where s.pool = 'shared pool' and s.name = 'free memory'
and sps.name='shared_pool_size'
and sps_reserved.name='shared_pool_reserved_size'
;