| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_dell.com <mailto:gautam_reddy_at_dell.com>
www.dell.com <http://www.dell.com/>
-----Original Message-----
From: Gautam_Reddy_at_dell.com [mailto:Gautam_Reddy_at_dell.com]
Sent: Tuesday, July 25, 2000 11:04 AM
To: oracledba_at_quickdoc.co.uk
Cc: ORACLE-L_at_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"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.00.2919.6307" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2><SPAN
class=879533120-25072000>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.</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>select</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS"
size=2>substr(decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>'BEING USED'),3,'BEING
USED',state),1,11) status,</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>substr(o.owner,1,15)
owner,</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>substr(o.object_name,1,30)
object,</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>count(*)</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>from</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>x$bh b,</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>dba_objects o</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>where</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>o.object_id = b.obj
and</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>o.owner not in
('SYS','SYSTEM')</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>group by</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS"
size=2>decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',</FONT></DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>'BEING USED'),3,'BEING
USED',state),</FONT></DIV>
<DIV><FONT size=2><FONT color=#800000><FONT face="Comic Sans MS">o.owner,
substr(o.object_name,1,30)<SPAN
class=879533120-25072000>;</SPAN></FONT></FONT></FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2>Thx</FONT></DIV>
<DIV><FONT face="Comic Sans MS" size=2><STRONG>Gautam
Reddy</STRONG></FONT></DIV>
<DIV><FONT face="Comic Sans MS"
size=2><STRONG>728-3656(512)</STRONG></FONT></DIV>
<DIV><FONT face="Comic Sans MS" size=2><STRONG><A
href="mailto:gautam_reddy_at_dell.com">gautam_reddy_at_dell.com</A></STRONG></FONT></DIV>
<DIV><FONT face=Arial size=2><A
href="http://www.dell.com/">www.dell.com</A></FONT></DIV>
<DIV> </DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Gautam_Reddy_at_dell.com
[mailto:Gautam_Reddy_at_dell.com]<BR><B>Sent:</B> Tuesday, July 25, 2000 11:04
AM<BR><B>To:</B> oracledba_at_quickdoc.co.uk<BR><B>Cc:</B>
ORACLE-L_at_fatcity.com<BR><B>Subject:</B> How to find no of blocks
<BR><BR></DIV></FONT>
<DIV><FONT color=#800000 face="Comic Sans MS" size=2><SPAN
class=229165615-25072000>How to find how many blocks of an
object are present in the DB Buffer area at a given time.
</SPAN></FONT></DIV> <DIV> </DIV> <DIV><FONT color=#800000 face="Comic Sans MS" size=2>Thx</FONT></DIV> <DIV><FONT face="Comic Sans MS" size=2><STRONG>Gautam</STRONG></FONT></DIV>Received on Tue Jul 25 2000 - 16:27:27 CDT
 |
 |