Quote Originally Posted by mdvreddy
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
Lets go step-by-step.

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.