The temporary tablespace in Oracle is used to store data that is temporary and session specific (sort operations, hash joins, temporary tables data, etc.).
Once a session needs to perform a large sort operation, part of the information is written to the temporary tablespace in a sort segment. The sort segment extents in the temporary tablspace are alloacted once, to prevent the database from performing many allocation and deallocation operations. Instead of deallocating the free extents, when the sort or join is completed, Oracle marks the extents and blocks as free.
This behavior is efficient but prevents us from decreasing the temp file size if large sort segments were allocated. To do so, we’ll have to create a new temporary tablespace, change the default temporary tablespace to the new tablespace (and if neccesary, users’ default temporary tablespace as well) and drop the old temporary tablespace.
Information about the sort segments can be found in V$SORT_SEGMENT dictionary view. This view contains one row for each sort segment and the following columns:
TOTAL_EXTENTS – The number of extents allocated to the sort segment
TOTAL_BLOCKS – The number of blocks allocated to the sort segment
USED_EXTENTS – number of extents that are currently being used
USED_BLOCKS – number of blocks that are currently being used
FREE_EXTENTS – number of extents that are allocated to the segment but currently marked as free
FREE_BLOCKS – number of blocks that are allocated to the segment but currently marked as free






