/*  pkgs_not_kept.sql                          */
/*    shows which packages are in shared pool  */
/*      that are not 'kept'                    */

set pagesize 2000

select substr(name,1,30) NAME, sharable_mem
    from v$db_object_cache
    where sharable_mem > 10000
    and (type = 'PACKAGE' or type = 'PACKAGE_BODY' or
    type = 'FUNCTION' or type = 'PROCEDURE') and kept = 'NO'
    order by 1;

