SET ECHO off 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    Needs to be run as SYS 
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Virag Saksena, Oracle Corporation      
REM    (c)1996 Oracle Corporation 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Reports statistics about the buffers in the SGA. It will print 
REM    the information about the types of buffers in the SGA, how many of 
REM    them are on the dirty queue and how many are not. 
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 

set numf 9,999,999,999
set lines 100 
col class form A10 
select decode(greatest(class,10),10,decode(class,1,'Data',2 
            ,'Sort',4,'Header',to_char(class)),'Rollback') "Class", 
       sum(decode(bitand(flag,1),1,0,1)) "Not Dirty", 
 sum(decode(bitand(flag,1),1,1,0)) "Dirty", 
       sum(dirty_queue) "On Dirty",count(*) "Total" 
from x$bh 
group by decode(greatest(class,10),10,decode(class,1,'Data',2 
         ,'Sort',4,'Header',to_char(class)),'Rollback') 
/