Show what is in cache script

This is a query to show what is in cache currently [code language=”sql”] 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 […]