The operation that require sort area or Temporary Tablespace

Whenever a sort occurs within a database it needs sort area. Primarily memory area is used to sort. If there is not sufficient memory then it is needed temporary segments where database writes data in order to sort. There are several operation which needs sort space. They are,

1)Index creation.
The CREATE INDEX statement causes the server process to sort the index values before building the tree. After the sort a final index is built in the tablespaces by using a temporary segment.

2)ORDER BY or GROUP BY clauses of SELECT statements.
The server process must sort on the values in the ORDER BY or GROUP BY clauses.

3)DISTINCT values of SELECT statements.
For the DISTINCT keyword, the data is at first sorted in order to eliminate duplicates.

4)UNION, INTERSECT or MINUS operations.
Servers need to sort the tables they are working on to eliminate duplicates.

5)Sort-Merge joins.
If no index is available, an equivalent-join request needs to perform full table scans and sort each row source separately. After that, the sorted sources are merged together, combining each row from one source with each matching row of the other source.

6)Analyze command execution.
The Analyze command sorts the data to provide summarized information.

7)Various SQL Statements.
The CREATE PRIMARY KEY CONSTRAINT, ENABLE CONSTRAINT, and CREATE TABLE statements require sort segment.

8)CREATE TABLE AS SELECT.
The creation of a new table can start as a temporary segment if MINEXTENTS is larger than 1 or when using the statement CREATE TABLE AS SELECT.