DBA_DATA_FILES - Monitor Oracle Datafile Size & Growth

DBA_DATA_FILES

Overview

DBA_DATA_FILES contains one row for every datafile belonging to a PERMANENT or UNDO tablespace in the database. It is the primary view for understanding the physical storage layer: how large each file is, how much it can grow, whether autoextend is enabled, and what its current online status is. Temporary tablespace files are not included here — those appear in DBA_TEMP_FILES. DBAs join DBA_DATA_FILES with DBA_FREE_SPACE to produce comprehensive tablespace capacity reports and with DBA_TABLESPACES to add configuration context.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_DATA_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 datafile
FILE_ID	NUMBER	Unique file identifier within the database; used in joins to DBA_FREE_SPACE and DBA_EXTENTS
TABLESPACE_NAME	VARCHAR2(30)	Name of the tablespace this datafile belongs to
BYTES	NUMBER	Current size of the file in bytes
MAXBYTES	NUMBER	Maximum size the file can grow to if AUTOEXTENSIBLE = YES; 0 if autoextend is off
AUTOEXTENSIBLE	VARCHAR2(3)	YES if the file will extend automatically when the tablespace fills; NO otherwise
INCREMENT_BY	NUMBER	Number of Oracle blocks added each time the file autoextends
STATUS	VARCHAR2(9)	AVAILABLE or INVALID
ONLINE_STATUS	VARCHAR2(7)	ONLINE, OFFLINE, SYSOFF, SYSTEM, or RECOVER — the operational state of the file

Essential Queries

Datafile Inventory with Autoextend Information

List every datafile with its current size, maximum size, autoextend increment, and online status — the essential capacity inventory:

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

Tablespace Capacity Report — Used vs Free

Join DBA_DATA_FILES with DBA_FREE_SPACE to produce a per-tablespace capacity summary with used percentage:

SELECT
    d.tablespace_name,
    COUNT(d.file_id)                                    AS files,
    ROUND(SUM(d.bytes)              / 1073741824, 2)    AS allocated_gb,
    ROUND(NVL(SUM(f.free_bytes), 0) / 1073741824, 2)   AS free_gb,
    ROUND(
        (SUM(d.bytes) - NVL(SUM(f.free_bytes), 0))
        / 1073741824, 2)                                AS used_gb,
    ROUND(
        (SUM(d.bytes) - NVL(SUM(f.free_bytes), 0))
        / SUM(d.bytes) * 100, 1)                        AS pct_used,
    ROUND(SUM(d.maxbytes)           / 1073741824, 2)    AS max_possible_gb,
    SUM(CASE WHEN d.autoextensible = 'YES' THEN 1 ELSE 0 END) AS autoext_files
FROM
    dba_data_files d
    LEFT JOIN (
        SELECT tablespace_name, SUM(bytes) AS free_bytes
        FROM   dba_free_space
        GROUP BY tablespace_name
    ) f ON f.tablespace_name = d.tablespace_name
GROUP BY
    d.tablespace_name
ORDER BY
    pct_used DESC NULLS LAST;

Files Approaching Maximum Size

Identify datafiles that have consumed more than 80% of their autoextend maximum — critical for preventing ORA-01653 and ORA-01654 errors:

SELECT
    d.tablespace_name,
    d.file_id,
    d.file_name,
    ROUND(d.bytes    / 1073741824, 3)   AS current_gb,
    ROUND(d.maxbytes / 1073741824, 3)   AS max_gb,
    ROUND(d.bytes / NULLIF(d.maxbytes, 0) * 100, 1) AS pct_of_max,
    d.autoextensible,
    d.online_status
FROM
    dba_data_files d
WHERE
    d.autoextensible = 'YES'
    AND d.maxbytes  > 0
    AND d.bytes / d.maxbytes > 0.80
ORDER BY
    pct_of_max DESC;

Fixed-Size Files at Risk

Find non-autoextensible datafiles where the tablespace has less than 10% free space — these cannot grow automatically and will cause errors when full:

SELECT
    d.tablespace_name,
    d.file_id,
    d.file_name,
    ROUND(d.bytes / 1073741824, 3)              AS file_gb,
    ROUND(NVL(f.free_bytes, 0) / 1073741824, 3) AS free_gb,
    ROUND(NVL(f.free_bytes, 0) / d.bytes * 100, 1) AS pct_free,
    d.online_status
FROM
    dba_data_files d
    LEFT JOIN (
        SELECT file_id, SUM(bytes) AS free_bytes
        FROM   dba_free_space
        GROUP BY file_id
    ) f ON f.file_id = d.file_id
WHERE
    d.autoextensible = 'NO'
    AND NVL(f.free_bytes, 0) / d.bytes < 0.10
ORDER BY
    pct_free ASC;

Growth Monitoring via DBA_HIST_SEG_STAT

Estimate file-level growth trends by comparing current size against historical AWR snapshots:

SELECT
    d.tablespace_name,
    d.file_name,
    ROUND(d.bytes / 1073741824, 3)              AS current_gb,
    d.autoextensible,
    ROUND(d.maxbytes / 1073741824, 3)           AS max_gb,
    ROUND(
        (d.bytes / 1073741824)
        / GREATEST(
            TRUNC(SYSDATE) - (
                SELECT MIN(TRUNC(begin_interval_time))
                FROM   dba_hist_snapshot
            ), 1
        ), 4)                                    AS gb_per_day_since_awr
FROM
    dba_data_files d
WHERE
    d.autoextensible = 'YES'
ORDER BY
    gb_per_day_since_awr DESC NULLS LAST;

Common Use Cases

Capacity alerts — Schedule a query against this view to alert when any tablespace exceeds 85% used, giving time to add files or increase autoextend limits before an ORA-01653 occurs
Autoextend audit — Confirm that critical tablespaces such as SYSTEM, SYSAUX, and UNDO have autoextend enabled with sensible maximum sizes to prevent uncontrolled growth
Storage layout review — Verify that datafiles are spread across different mount points or ASM disk groups for I/O balance and fault tolerance
Pre-maintenance checks — Before large batch loads or index rebuilds, check that target tablespaces have enough headroom or can autoextend sufficiently
File relocation planning — Identify large datafiles on slow or almost-full mount points that should be moved using RMAN or ALTER DATABASE MOVE DATAFILE (12c+)
ORA-01653 / ORA-01654 troubleshooting — When a table or index extension fails, query this view to immediately see which datafile and tablespace is full and whether autoextend is configured

DBA_TABLESPACES — Tablespace-level configuration including extent management, contents type, and encryption settings
DBA_FREE_SPACE — Available free space within each tablespace; join on TABLESPACE_NAME or FILE_ID for per-file free space
DBA_SEGMENTS — All segments consuming space within these datafiles; use to find the largest objects in a full tablespace
DBA_TEMP_FILES — The equivalent view for temporary tablespace files, which are not included in DBA_DATA_FILES
V$SORT_USAGE — Current temp segment consumption per session; relevant when temp files are filling up
V$DATAFILE — Dynamic performance view showing the same files with real-time checkpoint and recovery information

Version Notes

Oracle 8i: AUTOEXTENSIBLE and INCREMENT_BY columns available; maximum file size introduced as a hard limit
Oracle 9i: No structural changes; datafile resizing via ALTER DATABASE DATAFILE RESIZE available for shrinking underused files
Oracle 10g: ONLINE_STATUS column added; 32 TB maximum file size on most platforms when using large block sizes
Oracle 11g: SHARED column added for shared server configurations; DBA_DATA_FILES_METADATA view introduced in some editions
Oracle 12c (Multitenant): FILE_ID values are now relative to the CDB; queries from a PDB see only files belonging to that PDB; ALTER DATABASE MOVE DATAFILE online move introduced
Oracle 19c: Online datafile move (ALTER DATABASE MOVE DATAFILE) became generally available and fully supported; no column changes
Oracle 23ai: No structural changes to DBA_DATA_FILES; improved storage management via enhanced DBMS_SPACE procedures