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.