Temp tablespace

Temp tablespace:

The sort segment expands by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance.

CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/oradata/imstest/temp01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

Temporary Segments
• Tempfiles are always set to NOLOGGING mode.
• You cannot make a tempfile read-only.
• You cannot rename a tempfile.
• You cannot create a tempfile with the ALTER DATABASE command.
• Tempfiles are required for read-only databases.
• Media recovery does not recover tempfiles.
• BACKUP CONTROLFILE does not generate any information for tempfiles.
• CREATE CONTROLFILE cannot specify any information about tempfiles.

Note: When creating a database without a default temporary tablespace the default tablespace,
assigned to any user created without a TEMPORARY TABLESPACE clause is the SYSTEM tablespace.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;