Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Viewing the Data Buffer Contents Script

The Oracle v$bh view shows the contents of the data buffers as well as the number of blocks for each type of segment in the buffer.  This view is primarily useful for indicating the amount of table and index caching in multiple data blocks.  Combining the v$bh view with dba_objects and dba_segments provides a block-by-block listing of the data buffer contents and indicates how well the buffers are caching tables and indexes.  This is very important in Oracle10g since the data buffer sizes can be altered dynamically.


There are several data dictionary tricks that can be used when writing a script for mapping data objects to RAM buffers:

§       Duplicate object names: When joining dba_objects to dba_segments, the name, type, and owner are all required to distinguish the object sufficiently. 

§       Multiple blocksizes: To show objects in the separate instantiated buffers such as db_2k_cache_size , etc., the block size for the object must be displayed.  This is achieved by computing the block size from dba_segments , dividing bytes by blocks.

§       Partitions: With a standard equi-join, every object partition joins to every segment partition for a particular object.  Hence, the following qualification is required to handle partitions:

and nvl(t1.subobject_name,'*') = nvl(s.partition_name,'*')

§       Clusters: Clusters present a challenge when joining the v$bh row with its corresponding database object.   Instead of joining the bh.objd to object_id, it needs to be joined into data_object_id  .

§       Multiple caches: There are situations where a particular block may be cached more than once in the buffer cache.  This is a mystifying concept, but it is easily overcome by creating the following in-line view:

(select distinct objd, file#, block# from v$bh where status != 'free')


Many thanks to Randy Cunningham for developing this great script to watch the data buffers.  His buf_blocks.sql script (below) only works with Oracle9i and beyond.


·       buf_blocks.sql


-- *************************************************

-- Copyright © 2005 by Rampant TechPress

-- This script is free for non-commercial purposes

-- with no warranties.  Use at your own risk.


-- To license this script for a commercial purpose,

-- contact

-- *************************************************


set pages 999

set lines 92


ttitle 'Contents of Data Buffers'


drop table t1;


create table t1 as


   o.owner          owner,

   o.object_name    object_name,

   o.subobject_name subobject_name,

   o.object_type    object_type,

   count(distinct file# || block#)         num_blocks


   dba_objects  o,

   v$bh         bh


   o.data_object_id  = bh.objd


   o.owner not in ('SYS','SYSTEM')


   bh.status != 'free'

group by





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



   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



   dba_segments s


   s.segment_name = t1.object_name


   s.owner = t1.owner


   s.segment_type = t1.object_type


   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')

group by






   sum(num_blocks) > 10

order by

   sum(num_blocks) desc




A sample listing from this exciting report is shown below.  The report lists the tables and indexes that reside inside the data buffer at the exact moment that the script was executed.  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.


The following is the report from buf_blocks.sql 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


This is an interesting report because there are three object types: tables; indexes; and partitions.  The subsets of the DEFAULT pool for KEEP and RECYCLE are also evident.  Also, all indexes are defined in the largest supported block size (db_32k_cache_size), and multiple buffer pools of 4K, 8K, 16K and 32K sizes are defined.


The output of this script can be somewhat confusing due to the repeated DEFAULT buffer pool name.  In earlier releases of Oracle, the KEEP and RECYCLE buffer pools are subsets of db_cache_size and can ONLY accommodate objects with the DEFAULT db_block_size.  In later releases, the KEEP and RECYCLE pools become independent pools.


Conversely, any block sizes that are NOT the default db_block_size, go into the buffer pool named DEFAULT.  The output listing shows that there are really six mutually exclusive and independently sized buffer pools, four of them are called DEFAULT.


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:




Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.