Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Does 9iR2 release temporary tablespace that is no longer used?

Does Oracle 9iR2 release temporary tablespace that is no longer being used? I am running it on Red Hat Linux (not Advanced Server). I only notice it being released for use again when I shut down the database and start it up again. The tablespace still exists at the high water mark it hit, but it is available for use in its entirety, again.

If it does not release, what would you recommend for running a series of processes that use a lot of the space iteratively on a server with limited disk? I have a temp tablespace create script that I could run between steps that creates "temptemp", sets it to the default, drops temp, creates temp, sets it to the default, then drops temptemp. The problems with this are it takes time to claim disk back up the the high water mark, it takes time to execute this script, etc. Ideally, Oracle should release the space for new sorts or whatever once the prior sort has finished, but I'm not seeing that happen. I am monitoring it in OEM, so perhaps it is happening but not registering in that GUI.

Any insights, advice, or resources you can point me to would be appreciated.

Oracle 9i does not release allocated TEMP segments until you shutdown the database. While the TEMP segment is allocated, it does not mean that it is unavailable for use. When a user requests a disk sort, Oracle will allocate a TEMP segment. Once that sort is done, Oracle releases this TEMP segment for future use, but does not deallocate it. When the next user requests a disk sort, Oracle does not have to allocate a new TEMP segment. It uses the same one that no user is currently using. Oracle manages this for you in 9i. And there is really only one TEMP segment in the TEMP tablespace. Multiple users can utilitize this one segment.

There have been problems in 9i where users running a TEMPORARY TEMP tablespace with TEMPFILES that is Locally Managed where Oracle does not release the sort space once it is no longer being used. This is a known bug. The workaround has been to revert back to Dictionary Managed tablespace for TEMP.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.