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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.
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.