Oracle9i New Feature Series: DEFAULT TEMPORARY TABLESPACE
Oracle9i introduced a new clause in the CREATE DATABASE and ALTER DATABASE statements to assign a default temporary tablespace for the database. When creating users, if you do not provide the TEMPORARY TABLESPACE clause, Oracle uses this tablespace as the users temporary tablespace. (The default for DEFAULT TABLESPACE for the user is still SYSTEM J).
When you ALTER the DEFAULT TEMPORARY TABLESPACE for the database, Oracle reassigns the temporary tablespace of the users with the default assignment to the new tablespace. Here is an example:
A default temporary tablespace cannot be taken offline until a new default temporary tablespace is online
Here is something interesting from the Oracle documentation:
If you specify the EXTENT MANAGEMENT LOCAL clause for the
SYSTEM tablespace when creating a database, the database must have a default
temporary tablespace, because a locally managed