ckkeep.sql

PROMPT *******************************
PROMPT Stored Packages and Procedures
PROMPT *******************************

set linesize 180

col Owner format a8
col Kept format a4 heading "Pin?"
col last_ddl_time format a18
col executions format 9,999,999,999
col share_mem format 9,999,999
col locks format 9,999
col loads format 9,999
col type format a12
compute sum of share_mem on owner
compute sum of share_mem on report
break on owner skip 1 on report

SELECT SUBSTR(x.owner,1,10) Owner,
       SUBSTR(x.name,1,30)  Name,
       x.type,
       x.sharable_mem       Share_Mem,
       x.executions,
       x.loads,
       SUBSTR(x.kept||' ',1,4)   Kept,
	y.status,
	to_char(y.last_ddl_time,'MM/DD/YY HH24:Mi:ss') LAST_DDL_time,
       x.locks
  FROM v$db_object_cache x, all_objects y
 WHERE x.type in ( 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE')
	and x.name = y.object_name (+)
	and x.type = y.object_type(+)
	and x.owner = y.owner(+)
 ORDER BY owner,name,status, type
/