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

Temp tablespace on Oracle 9i not releasing space

I have a temp tablespace on Oracle 9i Enterprise Edition running on Sun Solaris. The problem is that the temp tablespace is not releasing space so it is contunuosly growing. The tablespace datafile has been created as a tempfile. How can I resolve this?

If you create your temporary tablespace with tempfiles, then Oracle will not release the space. When you query DBA_SEGMENTS, you will see sort segments in this tablespace. Instead of dropping those segments, Oracle keeps them around and reuses them for future sort operations.

Unfortunately, there is a bug in Oracle that can stop these segments from being reused. This bug is most commonly seen with web applications. My first suggestion is to apply the latest patchset. The other suggestion is to drop the temporary tablespace and recreate it with datafiles, not tempfiles.


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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close