Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Temporary tablespace growth

Re: Temporary tablespace growth

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: 6 Dec 2006 12:06:55 -0800
Message-ID: <1165435615.790615.179620@80g2000cwy.googlegroups.com>

hgha wrote:
> my temporary tablespace has grown to it's maximum (32GB) in two
> separate incidents. is there
> any way I can find out what query has been responsible for this growth?
> V$sort_usage doesn't tell me much.
>
> thanks

My experience is that you need to catch the "culprits" while they are still running and chewing up TEMP tablespace instead of after the fact.  Depending on what version you are running etc some of the new GUI based reporting might be able to find this information after the fact.

Hmm ... wonder if new OEM in 10g does automatic alerting for any queries over some threshold of space in TEMP usage.

I have had a couple of instances ( prior jobs ) where I had to have something monitor periodically on any queries using more than a given percent of TEMP. ( It's usually someone trying to do ad hoc reporting and throwing in bad untested sql and/or cartesian joins in an OLTP environment ).

Then it can get political depending on who it is running the stuff. Just because you can see it occurring doesn't mean that you can kill it automatically sometimes.

If you want me to dig up the old script that I had reply to my email and I will see if I can hunt it down. Received on Wed Dec 06 2006 - 14:06:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US