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.
