Temporary Tablespace

Temporary Tablespace

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrence of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations
You can view the allocation and deallocation of space in a temporary tablespace sort segment

1.V$sort_segment
2.V$tempseg_usage
3.V$tempfile
4.dba_temp_files
5.V$temp_space_header

Creating a Locally Managed Temporary Tablespace

create temporary tablespace TEMP1
tempfile 'c:\oracle\product\10.1.0\oradata\catdb\temp02.dbf' size 3m reuse
autoextend off
uniform size 50k;

The AUTOALLOCATE clause is not allowed for temporary tablespaces


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

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