The Oracle (tm) Users' Co-Operative FAQ
My temporary tablespace never clears down, what should I do ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 29th Jan 2001 Oracle version(s): 7.3 - 8.1.7.0 |
| I have a temporary tablespace declared to have contents of type temporary (Oracle 7.3+) or declared as a proper (locally managed) temporary tablespace (Oracle 8.1+). I have found that Oracle never seems to free up any space that it uses in this temporary tablespace. What should I do about this ? |
In short, nothing. This is the expected behaviour of temporary tablespaces.
It used to be the case that when a process needed to do some sorting to disc it would allocate a segment, allocate extents as needed for sorting, then drop the segment when it had finished sorting. This could result in some stress on the space management portions of the data dictionary. Consequently Oracle re-wrote the mechanism so that a single segment and a pool of extents would be allocated (per temporary tablespace and per instance if using OPS) and managed by the instance. If a process needed space, it would be allocated extents as required from the pool. Extra extents were added to the pool only when necessary. The segment and pool of extents was released only when the instance shut down, and was cleaned up on the next startup.
The purpose of this strategy was to eliminate almost all the space management transactions that used to take place for sorting. The side-effect was that (in most cases) the single sort segment per tablespace could only grow. Consequently, in a well managed database, the temporary tablespaces inevitably gets close to full after the database has been running for a while.
Unfortunately, just to confuse the issue, there have been several bugs which have resulted in Oracle demanding enormous amounts of sort space, and this has given the impression that a full temporary tablespace is an indication that something has gone wrong.
Further reading: The article What Does SMON do ? by Jonathan Lewis also covers this issue; the article was written for Oracle 7.3.3, but still contains much that is relevant to Oracle 8.1.
