akshaykb1

 Profile Answers by akshaykb1 

• Mar 3rd, 2007

 

Data that is only used for the duration of a session is stored in a temporary tablespaces. Such data is for example the result of a sort (order by) operation. More specifically, the date is held in temporary segments.


It is normal if temporary tablespaces appear full after a while. This is because the extents are not managed in the data dictionary but in memory. The reason is simple: updating the data dictionary would be an expensive operation.

v$sort_usage and v$sort_segment can be used to find out who occupies the space in temporary Tablespaces.



Temporary segments
The following SQL statements require a temporary segment if they cannot be completed in memory:

    * create index.
    * select order by
    * select distinct
    * select group by
    * select union
    * select intersect
    * select minus by
    * analyze table
    * unindexed joins
    * certain correlated subqueries

Actually, most of these statements require a sort.
Temporary segments can be stored in any tablespace. However, it makes more sense to store temporary segments in a temporary tablespace.

--
Moid