Oracle Cache Contents

This is a really useful little query. Have you ever wondered how to check and see what is being cached in your oracle database? This query will tell you, at least in Oracle 10g. You have to be connected as sys to run it.

select
kcbwbpd.bp_name,
o.name,
count(*) BLOCKS
from sys.x$kcbwds kcbwds,
sys.x$kcbwbpd kcbwbpd,
sys.x$bh bh,
sys.obj$ o
where kcbwds.set_id >= kcbwbpd.bp_lo_sid
and kcbwds.set_id <= kcbwbpd.bp_hi_sid
and kcbwbpd.bp_size != 0
and o.dataobj# = bh.obj
and bh.state !=0
and o.owner# !=0
group by kcbwbpd.bp_name, o.name
order by kcbwbpd.bp_name, blocks desc, o.name