Doc

    Name:  spoolsiz.sql

    Author Mark Gurry

    This script shows the objects being stored in the shared pool
    in descending order of size.

#


set pagesize 999
column owner format a16
column name  format a36
column sharable_mem format 999,999,999
column executions   format 999,999,999
ttitle ' Memory Usage of Shared Pool Order - Biggest First'
select  owner, name||' - '||type name, sharable_mem 
  from v$db_object_cache
 where sharable_mem > 10000
   and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by sharable_mem desc;