Oracle Database - Temporary Datafiles (tempfiles)

> Database > Oracle Database

1 - 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 V$TEMPFILE, but not in DBA_DATA_FILES or the V$DATAFILE view.
Advertising

3 - How to

3.1 - 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;

3.2 - Drop a tempfile ?

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

4 - Documentation / Reference

db/oracle/tempfile.txt · Last modified: 2018/09/16 12:42 by gerardnico