Wednesday, July 18, 2012

How to identify buffer cache objects ?

SELECT   o.owner owner,
           o.object_name object_name,
           o.subobject_name subobject_name,
           o.object_type object_type,
           COUNT (DISTINCT file# || block#) num_blocks
    FROM   dba_objects o, v$bh bh
   WHERE       o.data_object_id = bh.objd
           AND o.owner NOT IN ('SYS', 'SYSTEM')
           AND bh.status != 'free'
GROUP BY   o.owner,
           o.object_name,
           o.subobject_name,
           o.object_type
ORDER BY   COUNT (DISTINCT file# || block#) DESC;

No comments:

Post a Comment