TTITLE “Total buffers used by individual objects.”
COL OWNER FOR A8
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A10
select obj.owner “OWNER”, obj.object_name “OBJECT_NAME”,
obj.object_type “OBJECT_TYPE”, COUNT(distinct bh.block#) “# Buffers”
from dba_objects obj, v$bh bh
where obj.object_id = bh.objd and
obj.owner != ‘SYS’
group by obj.owner, obj.object_name, obj.object_type
having COUNT(distinct bh.block#) >= 2
order by 4;