rem Name:    cache.sql
rem Purpose: Describe contents of objects in library cache
rem Usage:   @cache <%owner.name%> <%type%> <min_size>
rem Subject: tuning

@setup1

define typ="upper('&&2')"
define siz="decode('&&3','%','0','&&3')"

column oname format a25
column type format a12
column sharable_mem format 9,999,990 heading "SHRMEM"
column loads format 9990
column executions format 999,990 heading "EXECS"
column locks format 9990
column pins format 9990
column kept format a3

break on report
compute sum of sharable_mem on report

select
  owner||'.'||name||decode(db_link,null,'','@'||db_link) oname,
  type,
  sharable_mem,
  loads,
  executions,
  locks,
  pins,
  kept
from v$db_object_cache
where owner like &&o1
and name like &&n1
and type like &&typ
and sharable_mem>=to_number(&&siz)
order by owner,name
;

undef typ siz

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips