Oracle Database - Temporary Datafiles (tempfiles)

About

Tempfiles are a special class of datafiles that are associated only with temporary tablespaces.

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles, with the following exceptions:

• Tempfiles are always set to NOLOGGING mode.
• You cannot make a tempfile read only.
• You cannot create a tempfile with the ALTER DATABASE statement.
• Media recovery does not recognize tempfiles:
  • BACKUP CONTROLFILE does not generate any information for tempfiles.
  • CREATE CONTROLFILE cannot specify any information about tempfiles.
• When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX disk blocks are allocated not at file creation or resizing, but before the blocks are accessed. This enables fast tempfile creation and resizing; however, the disk could run out of space later when the tempfiles are accessed.
• Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view VTEMPFILE, but not in DBA_DATA_FILES or the VDATAFILE view.

Articles Related

How to

Move a tempfile ?

SHUTDOWN IMMEDIATE
STARTUP MOUNT
select name from v$tempfile;
ALTER DATABASE RENAME FILE 'E:\APP\ADMINISTRATOR\ORADATA\DB11GR2\TEMP01.DBF' TO 'F:\APP\ADMINISTRATOR\ORADATA\DB11GR2\TEMP01.DBF';
select name from v$tempfile;
ALTER DATABASE OPEN;

Drop a tempfile ?

ALTER DATABASE TEMPFILE 'F:\ORADATA\DB11GR2\TEMP02.DBF' DROP INCLUDING DATAFILES;

Documentation / Reference

• Temporary Datafiles
• ORA-1652: Error Troubleshooting
• How Do You Find Who And What SQL Is Using Temp Segments ?