This discussion is archived
3 Replies Latest reply: May 10, 2013 4:19 PM by Alvaro RSS

Temporary tablespace maintenance question

user1064 Newbie
Currently Being Moderated
Oracle 11g R2 standard edition
Windows 2008 R2 64-bit


When we create a temporary tablespace in oracle database we are assigning the temp file max size 60GB.


When the size is full we are creating a new temporary table space and move all users to new temporary tablespace and then delete old temp tablespace.


Questions:


1). Doesn't Oracle release the free temporary tablespace back to operating system ?

2). Does Oracle release free temp space back to OS when we restart the database ?


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 ). ?


Thanks in advance
  • 1. Re: Temporary tablespace maintenance question
    sb92075 Guru
    Currently Being Moderated
    Temporary tablespace requires NO maintenance.
  • 2. Re: Temporary tablespace maintenance question
    TSharma-Oracle Guru
    Currently Being Moderated
    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.
  • 3. Re: Temporary tablespace maintenance question
    Alvaro Pro
    Currently Being Moderated
    Hello,

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points