This content has been marked as final. Show 3 replies
1). Doesn't Oracle release the free temporary tablespace back to operating system ?NO
2). Does Oracle release free temp space back to OS when we restart the database ?NO
3) how do all the DBA pros maintain there temporary tablespace ? Do the same as we are doing currently ( create new one and delete old temp tablespace ). ?If you know that your tablespace will be filled again upto the same size then why do you want to shrink it. So, like the user above said, it generally need no maintenance. Sometime, you should be able to shrink the tempfile provided there is nothing at the end of the file.
1) Despite the name, a temporary tablespace is very permanent. The only thing temporary are the segments within them to support operations that sessions execute and can not fit into memory (hash join, sorts, etc).
2) Nope, it doesn't. Oracle NEVER shrinks datafiles automatically. Once your temporary tablespace grows the datafile, that's it.
3) See above. It requires no maintenance, SMON automatically does the housekeeping job with the temporary segments within the temporary tablespace. The only thing we have to do is to ensure the database has enough temporary tablespace so that sessions do not enqueue waiting for a free temp segment. This tipically manifests itself as sessions wait on enq: TS contention.
As a side note, there is no reason why you should be doing what you are doing. If you want more control over the growth of your temp tablespace, create it with a fixed size and do no let it autoextend. From there you can monitor the workload and add more datafiles to it if you judge that the initial size wasn't enough.