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

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.