Default Temporary Tablespace

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.

If you specify EXTENT MANAGEMENT LOCAL but you do not specify the DATAFILE clause, you can omit the default_temp_tablespace clause. Oracle will create a default temporary tablespace called TEMP with one datafile of size 10M with autoextend disabled.
If you specify both EXTENT MANAGEMENT LOCAL and the DATAFILE clause, then you must also specify the default_temp_tablespace clause and explicitly specify a datafile for that tablespace.

 

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.