Temporary Tablespace

Temporary tablespace is used for sorting large tables. Every database should have one temporary tablespace. A temporary tablespace is usually created at the time of Database Creation. However you can also create temporary tablespace afterwards.

To create temporary tablespace give the following command.

SQL> create temporary tablespace temp tempfile ‘/u01/oracle/data/ica_temp.dbf’ size 100M extent management local  uniform size 5M;

The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size.  The AUTOALLOCATE clause is not allowed for temporary tablespaces.

Increasing or Decreasing the size of a Temporary Tablespace

You can use the resize clause to increase or decrease the size of a temporary tablespace. The following statement resizes a temporary file:

SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

The following statement drops a temporary file and deletes the operating system file:

SQL> ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP  INCLUDING DATAFILES;

Tablespace Groups

A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics:

• It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.
• It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
• You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.

You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.

Creating a Temporary Tablespace Group

You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.

For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

CREATE TEMPORARY TABLESPACE ica_temp2 TEMPFILE '/u02/oracle/ica/ica_temp.dbf'
  SIZE 50M TABLESPACE GROUP group1;

ALTER TABLESPACE ica_temp2 TABLESPACE GROUP group2;

Assigning a Tablespace Group as the Default Temporary Tablespace

Use the ALTER DATABASE ...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:

To view information about Temporary Tablespaces and Tempfiles

SQL>select * from dba_temp_files;
SQL>select * from v$tempfile;

To view information about free space in tempfiles

SQL>select * from V$TEMP_SPACE_HEADER;