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@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Juan Carlos Reyes
Pacheco
Sent: Wednesday, July 21, 2004 3:03 PM
To: oracle-l@(protected)
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@(protected)
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
