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:

SQL> select property_value from database_properties

  2  where PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_VALUE

-----------------------------------

SYSTEM

 

1 row selected.

 

SQL> create user test_biju identified by test;

 

User created.

 

SQL> select default_tablespace, temporary_tablespace

  2  from dba_users where username = 'TEST_BIJU';

 

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYSTEM                         SYSTEM

 

1 row selected.

 

SQL> alter database default temporary tablespace temp;

 

Database altered.

 

SQL> select default_tablespace, temporary_tablespace

  2  from dba_users where username = 'TEST_BIJU';

 

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYSTEM                         TEMP

 

1 row selected.

 

SQL>

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 SYSTEM tablespace cannot store temporary segments.