Hi all,
i am trying to monitor the temp tablespace for transactions.
db: oracle 9.2.0.3
temp : 1GB
platform: Win XP.

thanks

jerry dev wrote:
> Hi all,
> i am trying to monitor the temp tablespace for transactions.
> db: oracle 9.2.0.3
> temp : 1GB
> platform: Win XP.
>
> thanks

Sounds like a horrifying waste of time: Why?

And why 9.2.0.3? You should be at 9.2.0.4 or 9.2.0.6.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan schrieb:
> jerry dev wrote:
>
>> Hi all,
>> i am trying to monitor the temp tablespace for transactions.
>> db: oracle 9.2.0.3
>> temp : 1GB
>> platform: Win XP.
>>
>> thanks
>
>
> Sounds like a horrifying waste of time: Why?

Don't think so, as every kind of resource , temp space might be
sometimes worth to monitor as well.
I've seen in some big DWH environments sometimes overfilling of temp
just because users written bad queries resulting in a crossjoin ( not
that stupid users, just huge aggregation in terms of lines of code plus
human errors) , sometimes optimizer prefer to filter resultset of
crossjoin as the cheapest plan ( mostly due to wrong statistics ) etc.
Can get worse if you have tempfile autoxtend on...
For my needs this query worked well enough...

select sum(a.mb) mb,a.segtype,b.username,b.osuser
from
(select sum(blocks)*8/1024 mb,session_addr ,segtype
from v$sort_usage
group by session_addr,segtype) a, v$session b
where a.session_addr=b.saddr
group by b.username,b.osuser,a.segtype

>
> And why 9.2.0.3? You should be at 9.2.0.4 or 9.2.0.6.


Best regards

Maxim