The temporary tablespace (by default TEMP with the data file TEMP01.DBF) stores temporary tables and indexes created during the processing of your SQL statement. If you are running a SQL statement that involves a lot of sorting, such as the constructs GROUP BY, ORDER BY, or DISTINCT, then you may need to expand this tablespace.
A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.
When you specify a “temporary” tablespace, the tablespace cannot be used to hold any permanent segments; it can only be used for temporary segments created during queries. The first sort to use the temporary tablespace allocates a temporary segment within the temporary tablespace; when the query completes, the space used by the temporary segment is not dropped. Instead, the space used by the temporary segment is available for use by other queries; this allows the sorting operation to avoid the costs of allocating and releasing space for temporary segments. This will reduce the temporary segment lock allocation contention and will save the cost of the 10 INSERT/UPDATE/DELETE statements involved in temporary segment allocation.
To dedicate a tablespace as a temporary tablespace, specify TEMPORARY in the CREATE TABLESPACE or ALTER TABLESPACE command. The example shown in the following listing changes an existing tablespace named TEMP_1 to a TEMPORARY tablespace.
alter tablespace TEMP_1 temporary;
To change the TEMP_1 tablespace out of TEMPORARY state, and allow permanent objects to be stored in it, use the PERMANENT keyword in the ALTER TABLESPACE command, as shown in the following listing.
alter tablespace TEMP_1 permanent;