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@xxxxxxxxxxxxx Date: 07/21/04 15:51:15 To: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Juan Carlos Reyes Pacheco Sent: Wednesday, July 21, 2004 3:03 PM To: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------