package_memory.sql

-- package_memory.sql  --  This program will display used SGA memory for
--                         triggers, packages, and procedures.
-- Written by John Doe

set pagesize 60
column executions format 999,999,990
column Mem_used format   999,999,990
column type format   a10

select substr(owner,1,10) Owner,
       substr(type,1,12)  Type,
       substr(name,1,23)  Name,
       executions,
       sharable_mem       Mem_used,
       substr(kept||' ',1,4) "Kept?"
from v$db_object_cache
where type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
order by executions desc;