SQL Script DB Block Buffer

Updated: 03-Jun-2005
Version: 2.0

Description

This script lists the state of all the DB_BLOCK_BUFFERS in the database. This script must be run as SYS.

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set serverout on size 1000000
set verify off

break on report
compute sum of Total on report

spool buffer.lst

select decode(state, 0, 'Free', 
	1, decode(lrba_seq,0,'Available','Being Modified'), 
	2, 'Not Modified', 
	3, 'Being Read',
	'Other') "BLOCK STATUS"
	,count(*) Total
from sys.x$bh
group by decode(state, 0, 'Free', 
	1, decode(lrba_seq,0,'Available','Being Modified'), 
	2, 'Not Modified', 
	3, 'Being Read',
	'Other') 
/

select status, count(*)
from v$bh
group by status
/

select object_name, count(*)
from v$bh, dba_objects
where object_id = objd
group by object_name
/

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') 
/ 

set verify on
spool off

Related Links

Oracle 9i: (SQL Script) Objects in DB Cache

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links