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.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.