Message-Id: <10569.112946@fatcity.com> From: Gautam_Reddy@Dell.com Date: Tue, 25 Jul 2000 16:27:27 -0500 Subject: RE: How to find no of blocks This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BFF67F.217CF4E0 Content-Type: text/plain; charset="iso-8859-1" Took me the whole morning to get this query. Here is the script of what I asked for. Can anyone validate and let me know. select substr(decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE', 'BEING USED'),3,'BEING USED',state),1,11) status, substr(o.owner,1,15) owner, substr(o.object_name,1,30) object, count(*) from x$bh b, dba_objects o where o.object_id = b.obj and o.owner not in ('SYS','SYSTEM') group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE', 'BEING USED'),3,'BEING USED',state), o.owner, substr(o.object_name,1,30); Thx Gautam Reddy 728-3656(512) gautam_reddy@dell.com www.dell.com -----Original Message----- From: Gautam_Reddy@dell.com [mailto:Gautam_Reddy@dell.com] Sent: Tuesday, July 25, 2000 11:04 AM To: oracledba@quickdoc.co.uk Cc: ORACLE-L@fatcity.com Subject: How to find no of blocks How to find how many blocks of an object are present in the DB Buffer area at a given time. Thx Gautam ------_=_NextPart_001_01BFF67F.217CF4E0 Content-Type: text/html; charset="iso-8859-1"
Took me the whole morning to get this query. Here is the script of what I asked for. Can anyone validate and let me know.
 
select
substr(decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
'BEING USED'),3,'BEING USED',state),1,11) status,
substr(o.owner,1,15) owner,
substr(o.object_name,1,30) object,
count(*)
from
x$bh b,
dba_objects o
where
o.object_id = b.obj and
o.owner not in ('SYS','SYSTEM')
group by
decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
'BEING USED'),3,'BEING USED',state),
o.owner, substr(o.object_name,1,30);
 
 
Thx
Gautam Reddy
728-3656(512)
gautam_reddy@dell.com
www.dell.com
 
-----Original Message-----
From: Gautam_Reddy@dell.com [mailto:Gautam_Reddy@dell.com]
Sent: Tuesday, July 25, 2000 11:04 AM
To: oracledba@quickdoc.co.uk
Cc: ORACLE-L@fatcity.com
Subject: How to find no of blocks

How to find how many blocks of an object are present in the DB Buffer area at a given time.
 
Thx
Gautam