Oracle --buffer cache 对象所有者，名称， 类型，总大小，cache大小

--buffer cache 对象所有者，名称， 类型，总大小，cache大小
column c1 heading "Object|Name" format a30 truncate
column c2 heading "Object|Type" format a12 truncate
column c3 heading "Number of|Blocks" format 999,999,999,999
column c4 heading "Percentage|of object|data blocks|in Buffer" format 999
break on report
compute sum of c3 on report
select owner,
       object_name,
       object_type,
       num_blocks,
       sum(blocks),
       trunc((num_blocks / decode(sum(blocks), 0, .001, sum(blocks))), 4) * 100 || '%' -- buffer中的数据块比例
  from (select o.owner owner_name,
               o.object_name object_name,
               o.object_type object_type,
               count(1) num_blocks
          from dba_objects o, v$bh bh
         where o.object_id = bh.objd
           and o.owner not in ('SYS', 'SYSTEM')
         group by o.object_name, o.object_type, o.owner
         order by count(1) desc) t1,
       dba_segments s
where s.segment_name = t1.object_name
   and s.owner = t1.owner_name
   and num_blocks > 10
group by object_name, object_type, num_blocks, owner
order by num_blocks desc;