Temporary Segments

As its name suggest that this segment is used to hold temporary data. The prime example of this is sorting. So you can say that temporary segments are used when the database does any kind of sorting. When a database does sorting? There are a few events when a database needs to sort values.

1. Order by: Every time you use the command which includes the order by clause the database does sorting and intimately uses temporary segment. The example of an order by command is:

SQL>select * from emp order by ename;

This command will order the results by Employee Name in ascending order. If you want it to order it descending then you will give the command:

SQL>select * from emp order by ename desc;

2. Group by: When you use the clause of group by the database also performs the sorting. The group by clause is used to take aggregate values on a column. The example of group by clause is:

SQL>select dept no, count(*) from emp group by dept no;

3. Distinct: Distinct keyword is used to avoid the multiple show of a single value in a result. If you use command which includes this keyword the database does the sorting. The example of this is:

SQL>select distinct dept no from emp;

4. Creation of an Index: Every time you create an index the database does the sorting. Indexes are created for easy retrieval of data.
5. Union: Union operator is used when you need to merge the outputs of two commands. The use of union operator also involves the sorting process. The example of this is:

SQL>select * from emp union select * from employee;

These are the five situations when the database does the process of sorting. And when it does it stores some values for some time and it stores those values in the temporary segment.

Temporary segments are created inside the temporary tablespace. If you check the V$tablespace data dictionary view you will see the temporary tablespace named as temp. Usually these segments are created one per transaction. And also they are created at run time. Oracle is very clever it creates the temporary segments in advance for the maximum load. In this manner it avoids the creation of these segments at run time and intimately improves performance. The corresponding data file for the temporary tablespace is temp file and it can be seen on the operating system level. If you want more information about the temporary segments on your database then check the data dictionary view named as v$sort_segment.