Oracle Database - Temporary Datafiles (tempfiles)

Card Puncher Data Processing

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.

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





Discover More
Card Puncher Data Processing
Oracle Database - Buffer IO (Logical IO)

A buffer is a container for data. A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache. When a requested buffer is not found in memory, the database performs...
Card Puncher Data Processing
Oracle Database - Control Files

Every Oracle database has a control file. Every time an instance of an Oracle database is started, its control file identifies the physical structure: the datafiles, the tempfiles, and redo...
Card Puncher Data Processing
Oracle Database - Physical Database Structures (Files)

Oracle Database files include: data files, temporary files, control files, the server parameter file, the password file. redo log files, Archived Redo Log Files Parameter Files Alert...
Card Puncher Data Processing
Oracle Database - Physical I/O

A physical read, I/O of table or index data places the block into the buffer cache. Then we perform a logical io to retrieve the block. Hence, most physical read are immediately followed by a logical io....
Card Puncher Data Processing
Oracle Database - TableSpace

A tablespace is a logical data structure which is physically represented by one or more datafiles physically. From a analysis point of view, a tablespace is a group of one or more: physic datafile....
Card Puncher Data Processing
Oracle Database - Temporary Tablespace

The temporary tablespace (by default TEMP with the data file TEMP01.DBF) stores temporary tables and indexes created during the processing of your SQL statement. If you are running a SQL statement that...
Card Puncher Data Processing
Oracle database - File (Common Specification for data file, control file, )

The size of the file is in bytes but you can use: K (kilobytes), M (megabytes), G (gigabytes), or T (terabytes) Syntax: where: OFF Specify OFF to turn off autoextend if is turned on....



Share this page:
Follow us:
Task Runner