Liron Amitzi Oracle ACE

Oded Raz Oracle ACE Director

Oracle Temporary Tablespace Free Space

Posted by Liron Amitzi on Feb 7th, 2010 and filed under Administration, Oracle, Tips & Tricks. You can follow any responses to this entry through the RSS 2.0. You can leave a response or trackback to this entry

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

Leave a Reply

Security Code:

Log in / Advanced NewsPaper by Gabfire Themes