V$ASM_FILE - Track ASM File Sizes & Redundancy

V$ASM_FILE

Overview

V$ASM_FILE displays one row for every file stored within ASM diskgroups, providing the file number, size, redundancy setting, striping policy, and timestamps. Unlike a traditional file system where file paths are visible, ASM uses an internal numbering scheme: a file is fully identified by the combination of GROUP_NUMBER and FILE_NUMBER. DBAs use V$ASM_FILE primarily to audit space consumption by file type, verify that files have the expected redundancy after diskgroup changes, and map the internal ASM file numbers back to meaningful database file names by joining with views such as V$DATAFILE, V$LOGFILE, and V$CONTROLFILE.

Querying V$ASM_FILE requires connection to the ASM instance. The view does not expose the human-readable file path (e.g., +DATA/ORCLDB/DATAFILE/system.256.123456789); for that, query V$ASM_ALIAS alongside V$ASM_FILE.

View Type: Dynamic Performance View (ASM instance) Available Since: Oracle 10g Release 1 Required Privileges: SELECT on V_$ASM_FILE or SYSASM role or SELECT_CATALOG_ROLE

Key Columns

Column	Datatype	Description
GROUP_NUMBER	NUMBER	Diskgroup number containing this file; join key to V$ASM_DISKGROUP and V$ASM_DISK
FILE_NUMBER	NUMBER	File number within the diskgroup; together with GROUP_NUMBER forms the unique file reference
COMPOUND_INDEX	NUMBER	Combined index value (GROUP_NUMBER * 2^32 + FILE_NUMBER) used by some internal ASM APIs
INCARNATION	NUMBER	Incarnation number, incremented each time the file is recreated in the same slot
BLOCK_SIZE	NUMBER	Block size in bytes for this file; typically 512 bytes for most ASM files
BLOCKS	NUMBER	Number of blocks allocated to this file
BYTES	NUMBER	Logical size of the file in bytes (BLOCK_SIZE * BLOCKS)
SPACE	NUMBER	Actual physical space consumed on disk in bytes, including mirroring and metadata overhead
TYPE	VARCHAR2(64)	File type: DATAFILE, CONTROLFILE, ONLINELOG, ARCHIVELOG, TEMPFILE, BACKUPSET, PARAMETERFILE, DATAGUARDCONFIG, FLASHBACK, and others
REDUNDANCY	VARCHAR2(12)	Effective redundancy for this specific file: UNPROT, MIRROR, HIGH, or PARITY
STRIPED	VARCHAR2(8)	Striping policy: COARSE (1 MB stripes) or FINE (128 KB stripes)
CREATION_DATE	DATE	Timestamp when the file was created in ASM
MODIFICATION_DATE	DATE	Timestamp of the most recent modification to the file metadata

Essential Queries

Basic Usage

List all files in all diskgroups with their type, size, and physical space consumption:

SELECT
    g.name                                          AS diskgroup_name,
    f.file_number,
    f.type,
    f.redundancy,
    f.striped,
    ROUND(f.bytes / 1024 / 1024, 2)                 AS logical_mb,
    ROUND(f.space / 1024 / 1024, 2)                 AS physical_mb,
    ROUND((f.space - f.bytes) / 1024 / 1024, 2)     AS mirror_overhead_mb,
    ROUND(f.space / NULLIF(f.bytes, 0), 2)           AS space_amplification,
    f.creation_date,
    f.modification_date
FROM
    v$asm_file      f
    JOIN v$asm_diskgroup g ON g.group_number = f.group_number
ORDER BY
    g.name,
    f.type,
    f.bytes DESC;

Space Consumption by File Type

Aggregate physical space consumption by diskgroup and file type to understand what is driving space usage:

SELECT
    g.name                                          AS diskgroup_name,
    f.type,
    COUNT(*)                                        AS file_count,
    ROUND(SUM(f.bytes) / 1024 / 1024 / 1024, 3)    AS logical_gb,
    ROUND(SUM(f.space) / 1024 / 1024 / 1024, 3)    AS physical_gb,
    ROUND(SUM(f.space - f.bytes) / 1024 / 1024 / 1024, 3) AS mirror_overhead_gb,
    MIN(f.creation_date)                            AS oldest_file,
    MAX(f.modification_date)                        AS last_modified
FROM
    v$asm_file      f
    JOIN v$asm_diskgroup g ON g.group_number = f.group_number
GROUP BY
    g.name,
    f.type
ORDER BY
    g.name,
    SUM(f.space) DESC;

Map ASM File Numbers to Database File Paths

Join V$ASM_FILE with V$ASM_ALIAS to resolve the internal file number to the human-readable ASM file path, useful for correlating ASM files with their database role:

SELECT
    g.name                                          AS diskgroup_name,
    f.file_number,
    f.type,
    f.redundancy,
    ROUND(f.bytes / 1024 / 1024, 2)                 AS logical_mb,
    ROUND(f.space / 1024 / 1024, 2)                 AS physical_mb,
    '+' || g.name || '/' || d.name || '/'
        || a.name                                   AS asm_path_fragment
FROM
    v$asm_file          f
    JOIN v$asm_diskgroup    g ON g.group_number = f.group_number
    JOIN v$asm_alias        a ON a.group_number = f.group_number
                             AND a.file_number  = f.file_number
                             AND a.system_created = 'Y'
    JOIN v$asm_alias        d ON d.group_number = a.group_number
                             AND d.reference_index = a.parent_index
WHERE
    f.type IN ('DATAFILE', 'TEMPFILE', 'CONTROLFILE', 'ONLINELOG')
ORDER BY
    g.name,
    f.type,
    f.file_number;

Redundancy Mismatch Detection

Find files whose effective redundancy differs from the diskgroup default — these files may be under-protected or consuming more space than expected:

SELECT
    g.name                                          AS diskgroup_name,
    g.type                                          AS diskgroup_redundancy,
    f.file_number,
    f.type                                          AS file_type,
    f.redundancy                                    AS file_redundancy,
    ROUND(f.bytes / 1024 / 1024, 2)                 AS logical_mb,
    ROUND(f.space / 1024 / 1024, 2)                 AS physical_mb,
    f.creation_date
FROM
    v$asm_file      f
    JOIN v$asm_diskgroup g ON g.group_number = f.group_number
WHERE
    (g.type = 'NORMAL' AND f.redundancy NOT IN ('MIRROR', 'UNPROT'))
    OR (g.type = 'HIGH'   AND f.redundancy NOT IN ('HIGH',   'MIRROR', 'UNPROT'))
    OR (g.type = 'EXTERN' AND f.redundancy != 'UNPROT')
ORDER BY
    g.name,
    f.file_number;

Large File Identification

Find the largest files by physical space consumption, useful when a diskgroup is running low on space:

SELECT
    g.name                                          AS diskgroup_name,
    f.file_number,
    f.type,
    f.redundancy,
    f.striped,
    ROUND(f.bytes / 1024 / 1024 / 1024, 3)          AS logical_gb,
    ROUND(f.space / 1024 / 1024 / 1024, 3)          AS physical_gb,
    f.creation_date,
    f.modification_date
FROM
    v$asm_file      f
    JOIN v$asm_diskgroup g ON g.group_number = f.group_number
ORDER BY
    f.space DESC
FETCH FIRST 30 ROWS ONLY;

Common Use Cases

• Diskgroup space audit by file type — Aggregate SPACE by TYPE to determine how much physical storage is consumed by datafiles versus archive logs versus backupsets; archive logs and backup sets are frequent culprits when FRA diskgroups fill unexpectedly
• Redundancy validation after diskgroup conversion — After converting a NORMAL redundancy diskgroup to HIGH, or after using DBMS_FILE_TRANSFER, query REDUNDANCY per file to confirm all files have been re-mirrored to the new level
• Stale backup set identification — Files with TYPE = BACKUPSET and old MODIFICATION_DATE values may be orphaned backups not tracked by RMAN; cross-reference with V$BACKUP_PIECE before deleting
• Striping policy review — DATAFILE and TEMPFILE entries should generally show COARSE striping; FINE striping (128 KB) is used for control files and online redo logs; unexpected values may indicate non-default storage templates
• File creation timeline analysis — CREATION_DATE allows reconstruction of when database objects were added to ASM, useful in forensic investigations or when auditing database growth over time
• Mirror overhead estimation — The difference between SPACE and BYTES represents mirroring overhead; for NORMAL redundancy this approximates BYTES, so physical disk consumption is roughly 2x the logical database size

Related Views

• V$ASM_DISKGROUP — Top-level diskgroup space and status; query first before drilling into file-level detail
• V$ASM_DISK — Physical disk I/O and error statistics; join via GROUP_NUMBER
• V$ASM_ALIAS — Provides the human-readable path hierarchy within a diskgroup; essential for resolving FILE_NUMBER to a meaningful path
• V$DATAFILE — Database-side view of datafiles; join on FILE# and the ASM file number via V$ASM_ALIAS to correlate ASM entries with Oracle datafile numbers
• V$LOGFILE — Online redo log file paths; the ASM path listed here can be matched to V$ASM_FILE entries by querying V$ASM_ALIAS
• V$ASM_TEMPLATE — Default redundancy and striping templates applied when files are created; explains why a particular file has a given REDUNDANCY value

Version Notes

• Oracle 10g R1: View introduced; core columns GROUP_NUMBER through MODIFICATION_DATE available
• Oracle 10g R2: TYPE column expanded to include additional file categories such as DATAGUARDCONFIG and FLASHBACK
• Oracle 11g R2: STRIPED column values clarified; PARITY redundancy type introduced for Exadata Smart Scan compatibility
• Oracle 12c: CON_ID column added; in CDB environments, ASM files from all PDBs within a database are visible in aggregate
• Oracle 18c / 19c: FLEX diskgroup support adds the PARITY value to REDUNDANCY column; file-level redundancy override becomes more granular with FLEX diskgroups
• Oracle 21c / 23ai: No structural column changes; Sparse File support for Oracle Database In-Memory and Blockchain Table features may introduce new TYPE values in companion views