http://www.dba-oracle.com/art_so_oracle_display%20buffer_cache.htm

set pages 999
set lines 92

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
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
 See code depot download for full script 
 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
;

column c0 heading "Owner" format a12
column c1 heading "Object|Name" format a30
column c2 heading "Object|Type" format a8
column c3 heading "Number of|Blocks in|Buffer|Cache" format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer" format 999
column c5 heading "Buffer|Pool" format a7
column c6 heading "Block|Size" format 99,999

select
 t1.owner c0,
 object_name c1,
 case when object_type = 'TABLE PARTITION' then 'TAB PART'
 when object_type = 'INDEX PARTITION' then 'IDX PART'
 else object_type end c2,
 sum(num_blocks) c3,
 (sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
 buffer_pool c5,
 sum(bytes)/sum(blocks) c6
from
 t1,
 dba_segments s
where
 s.segment_name = t1.object_name
and
 s.owner = t1.owner
and
 s.segment_type = t1.object_type
and
 nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by
 t1.owner,
 object_name,
 object_type,
 buffer_pool
having
 sum(num_blocks) > 10
order by
 sum(num_blocks) desc
;

A sample listing from this exciting report is shown below. We can see that the report lists the tables and indexes that reside inside the data buffer. This is important information for the Oracle professional who needs to know how many blocks for each object reside in the RAM buffer. To effectively manage the limited RAM resources, the Oracle DBA must be able to know the ramifications of decreasing the size of the data buffer caches.

Here is the report from this script when run against a large Oracle data warehouse.

 Contents of Data Buffers

 Number of Percentage
 Blocks in of object
 Object Object Buffer Buffer Buffer Block
Owner Name Type Cache Blocks Pool Size
------------ -------------------------- ----------- ---------- ------- -------
DW01 WORKORDER TAB PART 94,856 6 DEFAULT 8,192
DW01 HOUSE TAB PART 50,674 7 DEFAULT 16,384
ODSA WORKORDER TABLE 28,481 2 DEFAULT 16,384
DW01 SUBSCRIBER TAB PART 23,237 3 DEFAULT 4,096
ODS WORKORDER TABLE 19,926 1 DEFAULT 8,192
DW01 WRKR_ACCT_IDX INDEX 8,525 5 DEFAULT 16,384
DW01 SUSC_SVCC_IDX INDEX 8,453 38 KEEP 32,768
DW02 WRKR_DTEN_IDX IDX PART 6,035 6 KEEP 32,768
DW02 SUSC_SVCC_IDX INDEX 5,485 25 DEFAULT 16,384
DW02 WRKR_LCDT_IDX IDX PART 5,149 5 DEFAULT 16,384
DW01 WORKORDER_CODE TABLE 5,000 0 RECYCLE 32,768
DW01 WRKR_LCDT_IDX IDX PART 4,929 4 KEEP 32,768
DW02 WOSC_SCDE_IDX INDEX 4,479 6 KEEP 32,768
DW01 SBSC_ACCT_IDX INDEX 4,439 8 DEFAULT 32,768
DW02 WRKR_WKTP_IDX IDX PART 3,825 7 KEEP 32,768
DB_AUDIT CUSTOMER_AUDIT TABLE 3,301 99 DEFAULT 4,096
DW01 WRKR_CLSS_IDX IDX PART 2,984 5 KEEP 32,768
DW01 WRKR_AHWO_IDX INDEX 2,838 2 DEFAULT 32,768
DW01 WRKR_DTEN_IDX IDX PART 2,801 5 KEEP 32,768