Space Allocation in a TEMPORARY Tablespace

Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users.To see the default temporary tablespace for a database:

SQL>SELECT * FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Space Allocation in a TEMPORARY Tablespace

You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$SORT_USAGE view identifies the current sort users in those segments.

When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just marked as free and available for reuse. The DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace

The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.

SELECT * from DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
----------------------------------- ------------------------------ ------------------------------- ------------------------
TEMP 250609664 250609664 249561088

Benefits Of TEMPORARY Tablespace:

Temporary tablespace group has the following benefits:

A temporary tablespace allows multiple default temporary tablespaces to be specified at the database level.
A temporary tablespace allows the user to use multiple temporary tablespaces in different sessions at the same time.
A temporary tablespace allows a single SQL operation to use multiple temporary tablespaces for sorting.