Temporary Tablespaces

Oracle8i enhanced the temporary tablespace feature and made it truly temporary. Prior to Oracle8i, the temporary tablespaces were created as you would create any other tablespace, the extent management was always DICTIONARY and Oracle used to format the entire data file when creating the tablespace. Now in 8i, you can create temporary tablespaces using the CREATE TEMPORARY TABLESPACE command.

The syntax of CREATE TEMPORARY TABLESPACE is almost similar to the CREATE TABLESPACE command, the significant difference is in specifying the file type - instead of datafile you now have to specify tempfile. Here is an example:

When you create the tablespace, Oracle does not allocate all the space for the datafile, it takes only a couple of bytes, so the tablespace creation is much faster even if you're creating a huge temporary tablespace. The file is formatted as and when temporary extents are allocated. You can specify the extent size while creating the tablespace. If you do not specify, the default size is 1MB.

The file can have the AUTOEXTEND feature also.

There is no ALTER TEMPORARY TABLESPACE command, you still use the ALTER TABLESPACE command to change the characteristics - and the only characteristic you can modify using the ALTER TABLESPACE command is the size of the temp file or to add more temp files. Any other change should require you to drop the tablespace and recreate it.

The reason for me saying "truly temporary" in the beginning, is that, there is no entry for the files belonging to the temporary tablespace in the control file. So you need not back it up or include it when recreating the control file. In case of recovery or recreating the control file, all you have to do is 

The DBA_TABLESPACES view shows locally managed temporary tablespaces also. For information on the temp files belonging to the tablespaces, you need to query the DBA_TEMP_FILES view.