This is a query to show what is in cache currently
select decode(pd.bp_id,1,'KEEP', 2,'RECYCLE', 3,'DEFAULT', 4,'2K SUBCACHE', 5,'4K SUBCACHE', 6,'8K SUBCACHE', 7,'16K SUBCACHE', 8,'32KSUBCACHE','UNKNOWN') subcache, bh.object_name, bh.blocks from x\$kcbwds ds, x\$kcbwbpd pd, (select /*+ use_hash(x) */ set_ds, o.name object_name, count(*) BLOCKS from obj\$ o, x\$bh x where o.dataobj# = x.obj and x.state !=0 and o.owner# !=0 group by set_ds,o.name) bh where ds.set_id >= pd.bp_lo_sid and ds.set_id <= pd.bp_hi_sid and pd.bp_size != 0 and ds.addr=bh.set_ds;