Oracle Database Temporary Tablespace Management.

To check for all the default tablespaces in your database 
execute the following query.

SQL> column property_name format a30
SQL> column property_value format a30
SQL> column description format a50

# The above statements are only for column formatting
# It's not necesarry to execute them, although
# they do make the display soothing to the eyes.

SQL>select * from database_properties where property_name
like '%TABLESPACE'; 

PROPERTY_NAME                  PROPERTY_VALUE  DESCRIPTION
------------------------------ --------------- -------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP            Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS           Name of default permanent tablespace

In many databases all the tablespaces have autoextend on by default.

SQL> select tablespace_name, file_name, autoextensible from
 dba_temp_files where tablespace_name='TEMP'


TABLESPACE_NAME  FILE_NAME                               AUTO
---------------  --------------------------------------- -------
TEMP             /u01/app/oracle/oradata/orcl/temp01.dbf YES



So because of this a rogue query or a sort may increase the size of
temporary tablespace from few hundred MBs to GBs and will end up 
eating all the space on the server.


In order to take care of this problem you have to recreate the 
tablespace to reclaim the extra space.

First of all if your temporary tablespace is the default one 
you will not be able to drop it.

If you try to do it you will encounter the following error.


SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

So, first make another temporary tablespace, make it default
then drop the old one.

EXAMPLE:

SQL> create temporary tablespace temp2
  2  tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 300m
  3  autoextend on next 30m maxsize 1000m
  4  extent management local;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> select *  from database_properties where property_name
 like '%TABLESPACE';

PROPERTY_NAME                PROPERTY_VALUE     DESCRIPTION
---------------------------- ------------------ ------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP2            Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS            Name of default permanent tablespace


SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.


Oracle 10g New Feature Temporary Tablespace Groups.

With temporary tablespace groups users can use more than one tablespace to
store their temporary segments.

A group is automatically created when a tablespace is assigned to it.

SQL> alter tablespace temp2 tablespace group temp_group;


Tablespace altered.

Adding another tablespace to the group



SQL> create temporary tablespace temp
  2  tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 100m
  3  autoextend on next 30m maxsize 1000m
  4  tablespace group temp_group;

Tablespace created.


Check the view for groups.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_GROUP                     TEMP
TEMP_GROUP                     TEMP2


The group can be assigned to any user or can be assigned  as the
default temporary tablespace.


# Assigning the group to user.

SQL> alter user scott temporary tablespace temp_group;

User altered.

# Make it the default temporary tablespace.

SQL> alter database default temporary tablespace temp_group;

Database altered.

SQL> select *  from database_properties where property_name
 like '%TABLESPACE';

PROPERTY_NAME                PROPERTY_VALUE   DESCRIPTION
---------------------------- ---------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP_GROUP     Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS          Name of default permanent tablespace


A tablespace can also be removed from the group.

SQL> alter tablespace temp2 tablespace group '';

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_GROUP                     TEMP


If you want this feature removed do the following.

# Switch the default from group to a tablespace.

SQL> alter database default temporary tablespace temp;

Database altered.

# Remove the tablespace from the group.


SQL> alter tablespace temp tablespace group '';

Tablespace altered.

# Since every member has been deleted from
# the group, the group will also
# cease to exist.

SQL> select * from dba_tablespace_groups;

no rows selected

11g New Feature temporary tablespace view.

SQL> select * from dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                  20971520        19922944   18874368


Check free space in temporary tablesplace.

SQL> select * from v$temp_space_header;

TABLESPACE_NAME                   FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO
------------------------------ ---------- ---------- ----------- ---------- ----------- ------------
TEMP                                    1   19922944        2432    1048576         128            1