Find Blocks in the Buffer cache

Use below  below query to find how many blocks  for each segment are currently in the buffer Cache.

RAC Database:


select o.owner, o.object_name,v.inst_id, count(*) number_of_blocks
from dba_objects o, gv$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name,v.inst_id
order by o.object_name,v.inst_id,count(*);

Non-RAC Database:


select o.owner, o.object_name, count(*) number_of_blocks
from dba_objects o, v$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name
order by o.object_name,count(*);