HIGH Temp tablespace usage
927467Sep 27 2012 — edited Sep 28 2012In our DB Oracle 11gR1 , We are facing a problem of high temporary tablespace usage..
Currently i made the temp tablespace size as 100GB, but im afraid, That is going to be used up too.
My concern is
1.When i checked in gv$sessions, gv$sql table, no SQL is running, which is using up temp spaces, But the current usage of temp space staands around 75%, i.e 75GB!.
I read on other forums that temp space may not be immediately freed. But 75GB is too much in my opinion
2. Is there any way to know which query is using up so high temp space? In 11gr2, There is a column DBA_HIST_ACTIVE_SESS_HISTORY.TEMP_SPACE_ALLOCATED which makes life easy. But anything of that sort, or any join in 11gr1 which can tell us which query used how much temp space?
3. Also what is the pattern of temp space usage in global temporary tables?. Some of our code contains usage of temporary tables, will the used tablespace re-allocated once the transaction is complete?
Thanks in anticipation of help.