DBA_TEMP_FILES

Oracle 11gR1

DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.

Column	Datatype	NULL	Description
FILE_NAME	VARCHAR2(513)		Name of the database temp file
FILE_ID	NUMBER		File identifier number of the database temp file
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace to which the file belongs
BYTES	NUMBER		Size of the file (in bytes)
BLOCKS	NUMBER		Size of the file (in Oracle blocks)
STATUS	CHAR(9)		File status: · AVAILABLE
RELATIVE_FNO	NUMBER		Tablespace-relative file number
AUTOEXTENSIBLE	VARCHAR2(3)		Indicates whether the file is autoextensible (YES) or not (NO)
MAXBYTES	NUMBER		maximum size of the file (in bytes)
MAXBLOCKS	NUMBER		Maximum size of the file (in Oracle blocks)
INCREMENT_BY	NUMBER		Default increment for autoextension
USER_BYTES	NUMBER		Size of the useful portion of the file (in bytes)
USER_BLOCKS	NUMBER		Size of the useful portion of the file (in Oracle blocks)

Note:

1.

select * from dba_temp_files;

FILE_NAME	FILE _ID	TABLESPACE _NAME	BYTES	BLOCKS	STATUS	RELATIVE _FNO	AUTOEXTENSIBLE	USER _BYTES	USER _BLOCKS
/u04//temp01.dbf	1	TEMP	2097152000	128000	AVAILABLE	1	NO	2096103424	127936
/u04//temp02.dbf	2	TEMP	2097152000	128000	AVAILABLE	2	NO	2096103424	127936
/u04//temp_app01.dbf	9	TEMP_APP	2097152000	128000	AVAILABLE	1	NO	2096103424	127936
/u04//temp_app02.dbf	10	TEMP_APP	2097152000	128000	AVAILABLE	2	NO	2096103424	127936
/u04//temp_app03.dbf	11	TEMP_APP	2097152000	128000	AVAILABLE	3	NO	2096103424	127936

2. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.

3. Example of creating temporary tablespace:

CREATE TEMPORARY TABLESPACE temp TEMPFILE ‘/u04/temp01.dbf’ SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

4. On some operating systems (such as Solaris), the database does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. The workaround is to move tempfiles to another place and move them back to have disk space allocated.

Oracle data dictionary views