RE: recycle and keep buffer in other tablespace block sizes

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 21 Jul 2004 15:53:41 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEAE0@bosmail00.bos.il.pqe>

Juan,

Did you read my previous posting?

In particular, the part where I said "I've figured out how to do this in 8i, but in 9i, I have not succeeded in writing such a query. I spent some time running in circles a few weeks ago, and never did get anywhere."

And then I detailed how my query works for 8i ONLY, and asked if anyone had worked it out for 9i........

I'll refrain from comment on the article referenced by that URL, except to say that I don't have much faith in the author's knowledge of Oracle.

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes Pacheco
Sent: Wednesday, July 21, 2004 3:03 PM
To: oracle-l_at_freelists.org
Subject: RE: recycle and keep buffer in other tablespace block sizes

Thanks Mark
but your query in 9i don't work, can't find ds.end_buf#

I found this few days ago
http://searchoracle.techtarget.com/tip/1,289483,sid41_gci992782,00.html

I tested this query in 9i , but I got nothing,=20 =20
select
t1.owner c0,
object_name c1,
case when object_type =3D 'TABLE PARTITION' then 'TAB PART' when object_type =3D '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
from
(select

o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,

count(distinct file# || block#) num_blocks from
dba_objects o,
v$bh bh
where
o.data_object_id =3D bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status !=3D 'free'
group by

o.owner,
o.object_name,
o.subobject_name,
o.object_type

order by
count(distinct file# || block#) desc)
t1,
dba_segments s
where
s.segment_name =3D t1.object_name
and
s.owner =3D t1.owner
and
s.segment_type =3D t1.object_type
and
nvl(s.partition_name,'-') =3D nvl(t1.subobject_name,'-') group by
t1.owner,

object_name,
object_type,
buffer_pool

having
sum(num_blocks) > 10
order by
sum(num_blocks) desc
;
=20
=20

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Wed Jul 21 2004 - 14:50:53 CDT