I read Mark
What I was asking why if the view has the same name from one release to
other
the number of fields change
"but your query in 9i don 't work, can 't find ds.end_buf# "
means "why things like things happens. "
Sorry If I didn 't express correctly
Why you say this
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. "
Personally in new topics, my two main source of knowledge are Kyte and
Burleson.
-- ----Original Message-- ----
From: oracle-l@(protected)
Date: 07/21/04 15:51:15
To: oracle-l@(protected)
Subject: RE: recycle and keep buffer in other tablespace block sizes
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........
-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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
