Printable View
The temp tablespace always fills up and Oracle does not clear it out. If I expand the size of it, it still fills up. Even if all processes are finished, Oracle does not clear it out. Then I have programs fail with the "Unable to extend" error on the temp tablespace. Could anyone suggest me how to overcome this?. thanks in advance
When ever user process requires memory which is not available it uses temp segment. For example if you use order by, group by,sum, min, max etc additional processing requires on the result set returned.
You can not do much to solve this issue. how ever you may check the code and ask the developer if they need this much data to be processed.
BTW how much is your sort_area_size and sort_area_retained_size and is Autoextend ON?
sort_area_size =0
sort_area_retained_size =65536
and Autoextend ON
Lets go step-by-step.Quote:
Originally Posted by mdvreddy
First, temp tablespace is supposed to show you the HWM; it doesn't mean all space under the HWM is in use, space is allocated which is great for performance purposes... Oracle doesn't have to go and allocate/deallocate space. That been said, temp tablespace showing a great deal of space allocation is not an issue, this is the expected behavior.
Second, when you get an "unable to extend" kind of error then you have a problem to be solved. Your temp space is certainly too small for your level of activity either because of it was set up too small or because of your queries are not properly fine tuned, therefore are using more temp space that needed.
I would monitor temp space and check which query/queries are actually eating your space up; once you know that piece of information you can certainly determine if more temp space is actually needed or if you have to work on your queries.