DBA_TEMP_FILES - Monitor Oracle Temp Tablespace Files

DBA_TEMP_FILES

Overview

DBA_TEMP_FILES contains one row for every tempfile belonging to a TEMPORARY tablespace in the database. Temporary tablespace files are distinct from permanent datafiles — they use a different allocation mechanism, do not appear in DBA_DATA_FILES, and their space is tracked separately from permanent free space. DBAs query DBA_TEMP_FILES to understand temp tablespace capacity, verify autoextend configuration, and join with V$SORT_USAGE or V$TEMPSEG_USAGE to diagnose ORA-01652 errors when sort, hash join, or global temporary table operations exhaust available temp space.

View Type: Data Dictionary View Available Since: Oracle 8 Required Privileges: SELECT on DBA_TEMP_FILES or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
FILE_NAME	VARCHAR2(513)	Full OS path and filename of the tempfile
FILE_ID	NUMBER	Unique file identifier within the database; used in joins to V$SORT_USAGE and V$TEMP_EXTENT_MAP
TABLESPACE_NAME	VARCHAR2(30)	Name of the temporary tablespace this file belongs to
BYTES	NUMBER	Current allocated size of the tempfile in bytes
MAXBYTES	NUMBER	Maximum size the tempfile can grow to if AUTOEXTENSIBLE = YES; 0 if autoextend is off
AUTOEXTENSIBLE	VARCHAR2(3)	YES if the file will extend automatically when temp space is needed; NO otherwise
INCREMENT_BY	NUMBER	Number of Oracle blocks added each time the file autoextends
STATUS	VARCHAR2(9)	ONLINE or OFFLINE

Essential Queries

Temp File Capacity Inventory

List all tempfiles with their current size, maximum size, and autoextend settings — the starting point for any temp space investigation:

SELECT
    tf.tablespace_name,
    tf.file_id,
    tf.file_name,
    ROUND(tf.bytes    / 1073741824, 3)          AS current_gb,
    tf.autoextensible                            AS autoext,
    ROUND(tf.maxbytes / 1073741824, 3)          AS max_gb,
    ROUND(
        (tf.maxbytes - tf.bytes) / 1073741824, 3
    )                                            AS remaining_headroom_gb,
    ROUND(tf.increment_by * t.block_size / 1048576, 1) AS increment_mb,
    tf.status
FROM
    dba_temp_files   tf
    JOIN dba_tablespaces t ON t.tablespace_name = tf.tablespace_name
ORDER BY
    tf.tablespace_name,
    tf.file_id;

Actual Temp Usage via V$SORT_USAGE

Join DBA_TEMP_FILES with V$SORT_USAGE to show how much temp space is currently allocated compared to total tempfile capacity:

SELECT
    tf.tablespace_name,
    ROUND(SUM(tf.bytes)                    / 1073741824, 2) AS total_temp_gb,
    ROUND(NVL(SUM(su.used_bytes), 0)       / 1073741824, 2) AS used_gb,
    ROUND(
        (SUM(tf.bytes) - NVL(SUM(su.used_bytes), 0))
        / 1073741824, 2)                                    AS free_gb,
    ROUND(
        NVL(SUM(su.used_bytes), 0)
        / SUM(tf.bytes) * 100, 1)                           AS pct_used,
    COUNT(DISTINCT tf.file_id)                              AS file_count
FROM
    dba_temp_files tf
    LEFT JOIN (
        SELECT tablespace, SUM(blocks * 8192) AS used_bytes
        FROM   v$sort_usage
        GROUP BY tablespace
    ) su ON su.tablespace = tf.tablespace_name
GROUP BY
    tf.tablespace_name
ORDER BY
    pct_used DESC NULLS LAST;

Temp Space Per Session

Identify which sessions are consuming the most temporary space — critical for diagnosing ORA-01652 and understanding which queries are driving high temp usage:

SELECT
    su.session_addr,
    su.session_num,
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.module,
    su.sqlhash,
    su.sql_id,
    su.tablespace                               AS temp_tablespace,
    ROUND(SUM(su.blocks) * 8192 / 1073741824, 3) AS temp_used_gb,
    ROUND(SUM(su.extents), 0)                   AS extents,
    su.segtype
FROM
    v$sort_usage su
    JOIN v$session  s ON s.saddr = su.session_addr
GROUP BY
    su.session_addr,
    su.session_num,
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.module,
    su.sqlhash,
    su.sql_id,
    su.tablespace,
    su.segtype
ORDER BY
    temp_used_gb DESC;

ORA-01652 Prevention Monitor

Proactively identify temp tablespaces that are at risk of running out of space, accounting for autoextend headroom — suitable for scheduling as a monitoring script:

SELECT
    tf.tablespace_name,
    ROUND(SUM(tf.bytes)    / 1073741824, 2)     AS allocated_gb,
    ROUND(NVL(SUM(su.used_bytes), 0) / 1073741824, 2) AS currently_used_gb,
    ROUND(
        (SUM(tf.bytes) - NVL(SUM(su.used_bytes), 0))
        / 1073741824, 2)                         AS currently_free_gb,
    ROUND(
        SUM(CASE WHEN tf.autoextensible = 'YES'
                 THEN tf.maxbytes - tf.bytes
                 ELSE 0
            END) / 1073741824, 2)                AS autoext_headroom_gb,
    ROUND(
        NVL(SUM(su.used_bytes), 0)
        / SUM(tf.bytes) * 100, 1)                AS pct_allocated_used,
    CASE
        WHEN NVL(SUM(su.used_bytes), 0) / SUM(tf.bytes) > 0.90
             AND SUM(CASE WHEN tf.autoextensible = 'YES'
                          THEN tf.maxbytes - tf.bytes
                          ELSE 0 END) < 1073741824
        THEN 'CRITICAL - ORA-01652 RISK'
        WHEN NVL(SUM(su.used_bytes), 0) / SUM(tf.bytes) > 0.75
        THEN 'WARNING - MONITOR CLOSELY'
        ELSE 'OK'
    END                                          AS risk_status
FROM
    dba_temp_files   tf
    LEFT JOIN (
        SELECT tablespace, SUM(blocks * 8192) AS used_bytes
        FROM   v$sort_usage
        GROUP BY tablespace
    ) su ON su.tablespace = tf.tablespace_name
GROUP BY
    tf.tablespace_name
ORDER BY
    pct_allocated_used DESC NULLS LAST;

Temp Tablespace Group Membership

Check whether temp tablespaces are members of a temp tablespace group (used to distribute temp space across groups in RAC or large databases):

SELECT
    tg.group_name,
    tg.tablespace_name,
    tf.file_id,
    tf.file_name,
    ROUND(tf.bytes    / 1073741824, 3)          AS current_gb,
    ROUND(tf.maxbytes / 1073741824, 3)          AS max_gb,
    tf.autoextensible,
    tf.status
FROM
    dba_tablespace_groups   tg
    JOIN dba_temp_files     tf ON tf.tablespace_name = tg.tablespace_name
ORDER BY
    tg.group_name,
    tg.tablespace_name,
    tf.file_id;

Common Use Cases

• ORA-01652 diagnosis — When users report sort or hash join failures, query DBA_TEMP_FILES and V$SORT_USAGE together to immediately see whether temp space is genuinely exhausted or misconfigured
• Temp tablespace sizing — After a major workload change (new large reports, data warehouse loads), measure peak temp usage over several days using V$SORT_USAGE to determine whether files need to be enlarged or added
• Autoextend verification — Confirm that all temp files have autoextend enabled with a sensible maximum before running large batch jobs, preventing failures during overnight processing windows
• Session-level blame analysis — When temp space consumption spikes, use the per-session query to identify the specific session and SQL_ID consuming runaway temp space, then tune or kill the query as appropriate
• Temp tablespace group auditing — In RAC environments, verify that each node has a temp tablespace group configured correctly so sessions are not constrained to a single shared temp tablespace
• Capacity planning — Track the ratio of currently used to total allocated temp space over time to determine when additional tempfiles will be required ahead of planned workload growth

Related Views

• DBA_TABLESPACES — Confirms the CONTENTS = TEMPORARY setting and extent management configuration for each temp tablespace
• DBA_DATA_FILES — The equivalent view for permanent and undo tablespace datafiles; temp files are excluded from DBA_DATA_FILES
• DBA_FREE_SPACE — Free space in permanent tablespaces; temp tablespace space availability is tracked separately via V$TEMP_SPACE_HEADER
• DBA_SEGMENTS — Permanent segment allocations; temporary segments created by sort and hash operations do not persist in DBA_SEGMENTS
• V$SORT_USAGE — Current active temporary segment allocations by session and SQL; the essential join partner for understanding live temp space consumption
• V$TEMP_SPACE_HEADER — Dynamic view showing current used and free space per tempfile header; alternative to the DBA_FREE_SPACE equivalent for temp tablespaces
• DBA_TABLESPACE_GROUPS — Lists temp tablespace group memberships for RAC multi-node temp distribution configurations

Version Notes

• Oracle 8: DBA_TEMP_FILES introduced when temporary tablespaces with dedicated tempfiles became available; before this, temporary segments were allocated in permanent tablespaces
• Oracle 9i: Locally managed temporary tablespaces became the default; space tracking via bitmap headers in tempfiles rather than the data dictionary
• Oracle 10g: Temp tablespace groups introduced (DBA_TABLESPACE_GROUPS); multiple temp tablespaces can be assigned to a group and users assigned to the group rather than an individual tablespace
• Oracle 11g: SHRINK SPACE option for temporary tablespaces introduced — ALTER TABLESPACE TEMP SHRINK SPACE can return unused temp space to the OS without recreating tempfiles
• Oracle 12c (Multitenant): Each PDB has its own temp tablespace; DBA_TEMP_FILES from a PDB shows only that PDB’s tempfiles; CON_ID column added for CDB-level queries
• Oracle 19c: No structural changes to DBA_TEMP_FILES; V$TEMPSEG_USAGE replaces V$SORT_USAGE as the preferred view for current temp usage in some monitoring scripts
• Oracle 23ai: No column additions; temp tablespace management behaviour unchanged; improved SQL in-memory caching may reduce temp space requirements for some workloads