RE: recycle and keep buffer in other tablespace block sizes

From: Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Wed, 21 Jul 2004 15:02:53 -0400
Message-Id: <40FEBDDD.00001F.00336@CACHITOSS>

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,  

select
t1.owner c0,
object_name c1,
case when object_type = 'TABLE PARTITION' then 'TAB PART' when object_type = '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 = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != '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 = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = 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
;    

---

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:05:36 CDT