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