SQL Script List Unpinned Objects in DB Cache

Updated: 20-Mar-2002
Version: 1.0

Description

List details of all packages, procedures, functions that are in the db_object_cache and therefore have been used recently, but are not pinned. 

This will provide a list of potential candidates for pinning in the shared pool

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

col owner form a8
col name form a26
col type form a12
col sharable_mem  form 9999999 heading "Size"

spool cache.lst

SELECT owner
,name
,type 
,sharable_mem      
,executions  
,loads    
FROM v$db_object_cache
  WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
   AND  executions > 0
   AND kept='NO'
  ORDER BY executions desc,
           loads desc,
           sharable_mem desc
/

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links