See the contents of the buffer cache

by Granville Bonyata on October 10, 2017

If you’re looking for what is in the buffer cache, use this:

select COUNT(*) ct, o.object_name, o.subobject_name, s.blocks
  from v$bh bh,
       dba_objects o,
       dba_segments s
WHERE o.data_object_id = bh.objd
  AND o.owner like 'SCHEMA_OWNER'
  AND o.owner = s.owner
  AND o.object_name = s.segment_name
  AND NVL(o.subobject_name,'x') = NVL(s.partition_name,'x')
  AND s.blocks > 0
  AND bh.status != 'free'
GROUP BY o.object_name, o.subobject_name, s.blocks

You can join it to dba_segments and see what percentage of the object is cached.

Note that the join is not on object_id, but data_object_id. Asktom has a nice explanation of the difference.

Short version: data_object_id starts as object_id, but if the storage segment changes, the data_object_id is updated.

Previous post:

Next post: