Oracle routine tasks

--> Clear Shared Pool
To clear the shared pool, issue the following:

Alter system flush shared_pool;

This does not clear shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages or triggers that are currently being executed or for SQL SELECT statements for which all rows have not yet been fetched.
It may be desirable to clear the shared pool before beginning performance analysis.

--> 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);

-->