RE: recycle and keep buffer in other tablespace block sizes
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jul 2004 15:53:41 -0400
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@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
-----------------------------------------------------------------
Other related posts:
