Objects in Data Buffers

Objects in Data Buffers:

set pages 50
set linesize 110

spool blocks.lst

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
select
   o.object_name    object_name,
--   o.subobject_name subobject_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.subobject_name,
   o.object_type
order by
   count(1) desc
/

column c1 heading "Object|Name"                 format a30
--column c1a heading "Partition|Name"             format a15
column c2 heading "Object|Type"                 format a16
column c3 heading "Number of|Blocks"            format 999,999,999,999
column c3a heading "Size (MB)|32k blocks"       format 999,999,999
column c4 heading "Percentage|of object|data blocks|in Buffer" format 999

select
   object_name       c1,
--   subobject_name    c1a,
   object_type       c2,
   num_blocks        c3,
   (num_blocks*32)/1024 c3a,
   (num_blocks/decode(sum(blocks), 0, .001, sum(blocks)))*100 c4
from
   t1,
   dba_segments s
where
   s.segment_name = t1.object_name
and
   num_blocks > 10
group by
   object_name,
--   subobject_name,
   object_type,
   num_blocks
order by
   num_blocks desc
/

exit
/

 
Sample Output:

Mon Aug 06                                                                       page    1
                                 Contents of Data Buffers

                                                                               Percentage
                                                                                of object
Object                         Object                  Number of    Size (MB) data blocks
Name                           Type                       Blocks   32k blocks   in Buffer
------------------------------ ---------------- ---------------- ------------ -----------
...

38 rows selected.