Cry How To...

Determine the amount of free space in the shared pool

To determine the amount of memory remaining in the shared pool:

Select s.name, s.bytes "Free Bytes",
Round((s.bytes/p.value)*100,2) "Perc Free",
p.value / (1024 * 1024) "SP Size MB"
from sys.v_$parameter p, sys.v_$sgastat s
where s.name = 'free memory'
and p.name = 'shared_pool_size';

This shows the total free space remaining in the shared pool, the percentage free and the total space. Alternately, the following shows how memory is being used in the shared pool:

select pool, name, sum(bytes) from v$sgastat
where pool like '%pool%'
group by rollup (pool, name);

These notes have been tested against Oracle 8.1.5