Temporarily Temporary Tablespace

Oracle uses temporary tablespace to perform any sorting required by the queries that have clauses mainly like order by, distinct, group by, union, interset and minus. Index creation and certain correlated subqueries will also use the temporary tablespace.

By default, if the temporary tablespace is not explicitly defined, oracle will use SYSTEM tablespace as the temporary tablespace.

Here is an example to create a temporary tablespace :

SQL> create temporary tablespace TEMP tempfile ‘/ora0001/oradata/temp_1.dbf’ size 500 M extent management local uniform size 256k;

A temporary tablespace can be made as default temporary tablespace for all the users in the database or can be assigned to specific users exclusively as the default one.

To make temp tablespace as default temporary tablespace for all the users in the database :
SQL> alter database default temporary tablespace temp;

To alter a user (e.g.scott) to use temp tablespace as temporary tablespace :
SQL> alter user scott temporary tablespace temp;

To specify temporary tablespace clause while creating a new user :
SQL> create user john identified by changeme
default tablespace users temporary tablespace temp1;

If you omit the TEMPORARY TABLESPACE clause, the temporary segments default to the SYSTEM tablespace. DBA can resize the temporary tablespace depending on the requirement for the temp space usage.

To resize the temp tablespace by adding new tempfile :
SQL> alter tablespace temp add tempfile ‘/ora0001/oradata/temp_2.dbf’ size 512m;

To resize an existing tempfile :
SQL> alter database tempfile ‘/ora0001/oradata/temp_1.dbf’ resize 1024M;

Sometimes, shrinking a temp tablespace may error out with
Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value

In such cases, it may be advisable to create a new temp tablespace with the required temp size and drop the old one using drop tablespace command. If the old tablespace is the default temporary, reassign the new tablespace as default one before dropping the old one.

The command for drop tablespace is :
SQL> drop tablespace temp including contents and datafiles;

Normally, sort operation will be done in memory based on the SORT_AREA_SIZE parameter size in the init.ora file. Any sorts exceeding the SORT_AREA_SIZE limit will acquire temporary segments.

Since temporary tablespaces do not contain any permanent objects, these tablespaces need not be backed up. Infact, tempfile information is not recorded in control file and tempfiles can be recreated any time.

If needed, DBA can make the tempfile offline or even can drop the tempfile as shown below :
SQL> alter database tempfile ‘/ora0001/oradata/temp1.dbf’ offline;

SQL> alter database tempfile ‘/ora0001/oradata/temp1.dbf’ drop including datafiles;