V$ASM_DISKGROUP - Monitor ASM Diskgroup Space & Status

V$ASM_DISKGROUP

Overview

V$ASM_DISKGROUP provides one row per diskgroup known to the ASM instance, summarising the name, state, redundancy type, total capacity, free space, and rebalance progress. It is the top-level view for ASM space management: DBAs check it first when a diskgroup goes offline, when a database reports ORA-01653 (unable to extend) or ORA-01652 (unable to extend temp segment), or when capacity planning requires an accurate picture of usable storage across all diskgroups.

The critical distinction in this view is between FREE_MB (raw free megabytes before mirroring overhead) and USABLE_FILE_MB (the actual free space available to store new database files after mirroring is accounted for). For NORMAL redundancy diskgroups, USABLE_FILE_MB is roughly half of FREE_MB; for HIGH redundancy, roughly one third. Always use USABLE_FILE_MB for capacity decisions.

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

Key Columns

Column	Datatype	Description
GROUP_NUMBER	NUMBER	Internal diskgroup identifier; used as a join key with V$ASM_DISK and V$ASM_FILE
NAME	VARCHAR2(30)	Diskgroup name as created with CREATE DISKGROUP (e.g., DATA, FRA, RECO)
STATE	VARCHAR2(11)	Current state: MOUNTED, DISMOUNTED, UNKNOWN, CONNECTED, or BROKEN
TYPE	VARCHAR2(6)	Redundancy type: EXTERN (no mirroring), NORMAL (2-way mirror), HIGH (3-way mirror), or FLEX
TOTAL_MB	NUMBER	Raw total size of all member disks in megabytes
FREE_MB	NUMBER	Raw free space across all member disks in megabytes, before mirroring overhead
USABLE_FILE_MB	NUMBER	Effective usable free space for new files after mirroring; this is the correct value for capacity decisions
REQUIRED_MIRROR_FREE_MB	NUMBER	Space that must remain free to restore full redundancy after losing the largest failure group
OFFLINE_DISKS	NUMBER	Number of disks currently offline in this diskgroup; any value above 0 is a critical alert condition
ALLOCATION_UNIT_SIZE	NUMBER	Allocation unit size in bytes (default 1 MB); affects performance characteristics of the diskgroup
VOTING_FILES	VARCHAR2(1)	Y if this diskgroup contains Grid Infrastructure voting files; impacts whether the diskgroup can be dismounted

Essential Queries

Basic Usage

Show all diskgroups with their state, redundancy, and both raw and usable free space:

SELECT
    g.group_number,
    g.name                                          AS diskgroup_name,
    g.state,
    g.type                                          AS redundancy,
    ROUND(g.total_mb              / 1024, 2)        AS total_gb,
    ROUND(g.free_mb               / 1024, 2)        AS raw_free_gb,
    ROUND(g.usable_file_mb        / 1024, 2)        AS usable_gb,
    ROUND(g.required_mirror_free_mb / 1024, 2)      AS mirror_reserve_gb,
    ROUND((1 - g.usable_file_mb / NULLIF(g.total_mb /
        CASE g.type
            WHEN 'EXTERN' THEN 1
            WHEN 'NORMAL' THEN 2
            WHEN 'HIGH'   THEN 3
            ELSE 2 END, 0)) * 100, 1)               AS pct_usable_used,
    g.offline_disks,
    g.voting_files
FROM
    v$asm_diskgroup g
ORDER BY
    g.name;

Space Monitoring with Alert Thresholds

Flag diskgroups where usable space falls below warning (20%) or critical (10%) thresholds — suitable for scheduled monitoring scripts:

SELECT
    g.name                                          AS diskgroup_name,
    g.type                                          AS redundancy,
    g.state,
    ROUND(g.total_mb        / 1024, 2)              AS total_gb,
    ROUND(g.usable_file_mb  / 1024, 2)              AS usable_gb,
    ROUND(g.usable_file_mb * 100.0 / NULLIF(
        CASE g.type
            WHEN 'EXTERN' THEN g.total_mb
            WHEN 'NORMAL' THEN g.total_mb / 2
            WHEN 'HIGH'   THEN g.total_mb / 3
            ELSE g.total_mb / 2
        END, 0), 1)                                 AS pct_usable_free,
    g.offline_disks,
    CASE
        WHEN g.offline_disks > 0                         THEN 'CRITICAL - DISK OFFLINE'
        WHEN g.usable_file_mb < 0                        THEN 'CRITICAL - BELOW MIRROR RESERVE'
        WHEN g.usable_file_mb * 100.0 / NULLIF(g.total_mb, 0) < 5  THEN 'CRITICAL'
        WHEN g.usable_file_mb * 100.0 / NULLIF(g.total_mb, 0) < 10 THEN 'WARNING'
        ELSE 'OK'
    END                                             AS alert_status
FROM
    v$asm_diskgroup g
ORDER BY
    g.usable_file_mb ASC;

Diskgroup Disk Member Detail

Join V$ASM_DISKGROUP with V$ASM_DISK to show the number of disks, failure groups, and I/O error counts alongside diskgroup-level space:

SELECT
    g.name                                          AS diskgroup_name,
    g.type                                          AS redundancy,
    g.state,
    COUNT(d.disk_number)                            AS total_disks,
    COUNT(DISTINCT d.failgroup)                     AS failure_groups,
    SUM(CASE WHEN d.mode_status = 'OFFLINE' THEN 1 ELSE 0 END) AS offline_disks,
    SUM(d.read_errs)                                AS total_read_errs,
    SUM(d.write_errs)                               AS total_write_errs,
    ROUND(g.total_mb       / 1024, 2)               AS total_gb,
    ROUND(g.usable_file_mb / 1024, 2)               AS usable_gb,
    g.allocation_unit_size / 1024 / 1024            AS au_size_mb
FROM
    v$asm_diskgroup g
    JOIN v$asm_disk d ON d.group_number = g.group_number
GROUP BY
    g.group_number,
    g.name,
    g.type,
    g.state,
    g.total_mb,
    g.usable_file_mb,
    g.allocation_unit_size
ORDER BY
    g.name;

Active Rebalance Monitoring

Show any diskgroup currently undergoing a rebalance operation, with estimated completion time sourced from V$ASM_OPERATION:

SELECT
    g.name                                          AS diskgroup_name,
    g.state,
    g.type,
    ROUND(g.usable_file_mb / 1024, 2)               AS usable_gb,
    g.offline_disks,
    o.operation,
    o.state                                         AS op_state,
    o.power,
    o.actual,
    ROUND(o.sofar)                                  AS sofar_mb,
    ROUND(o.est_work)                               AS est_total_mb,
    ROUND(o.est_minutes)                            AS est_minutes_remaining
FROM
    v$asm_diskgroup g
    LEFT JOIN v$asm_operation o ON o.group_number = g.group_number
ORDER BY
    g.name;

USABLE_FILE_MB Explained

Demonstrate the relationship between raw free space and usable space for each redundancy type, useful for documenting capacity to management:

SELECT
    g.name                                          AS diskgroup_name,
    g.type                                          AS redundancy,
    ROUND(g.total_mb              / 1024, 2)        AS raw_total_gb,
    ROUND(g.free_mb               / 1024, 2)        AS raw_free_gb,
    ROUND(g.required_mirror_free_mb / 1024, 2)      AS mirror_reserve_gb,
    ROUND(g.usable_file_mb        / 1024, 2)        AS usable_free_gb,
    CASE g.type
        WHEN 'EXTERN' THEN 'No mirroring — usable = raw free'
        WHEN 'NORMAL' THEN '2-way mirror — usable approx raw free / 2'
        WHEN 'HIGH'   THEN '3-way mirror — usable approx raw free / 3'
        WHEN 'FLEX'   THEN 'Variable mirror — see REQUIRED_MIRROR_FREE_MB'
        ELSE 'Unknown redundancy type'
    END                                             AS space_note
FROM
    v$asm_diskgroup g
ORDER BY
    g.name;

Common Use Cases

• Capacity planning — USABLE_FILE_MB is the definitive metric for how much database file space can still be allocated; present it alongside REQUIRED_MIRROR_FREE_MB to show the safety buffer required for failure group reconstruction
• Diskgroup mount failure diagnosis — When STATE is DISMOUNTED or BROKEN, query V$ASM_DISK where GROUP_NUMBER matches to find which disk is OFFLINE or in FAILOVER state; the diskgroup cannot mount until quorum is restored
• Proactive space alerting — A scheduled script that queries USABLE_FILE_MB and raises an alert below a threshold prevents ORA-01653 (table extension) and ORA-01652 (temp extension) errors in production databases
• Rebalance impact assessment — REQUIRED_MIRROR_FREE_MB shows how much space must remain free at all times; adding a disk when USABLE_FILE_MB is below this threshold risks leaving the diskgroup in degraded redundancy
• Grid Infrastructure voting disk validation — VOTING_FILES = ‘Y’ identifies diskgroups that contain voting files; these require special care during maintenance and cannot be dismounted while the cluster is running
• Allocation unit size tuning — ALLOCATION_UNIT_SIZE affects stripe granularity; 1 MB (default) suits OLTP while 4 MB or 8 MB is recommended for data warehouses and sequential-heavy workloads; document the current setting before changing it

Related Views

• V$ASM_DISK — Per-disk detail including path, I/O counters, and error counts; join on GROUP_NUMBER for a complete storage picture
• V$ASM_FILE — Files stored within each diskgroup; join on GROUP_NUMBER to break down space by file type
• V$ASM_OPERATION — Active rebalance, rebuild, and scrub operations; always query when a diskgroup is adding or dropping disks
• V$ASM_CLIENT — Database instances connected to this ASM instance and the diskgroups they have open; join on GROUP_NUMBER
• V$ASM_TEMPLATE — Default file redundancy and striping templates for each diskgroup; relevant when customising storage characteristics per file type
• V$ASM_DISKGROUP_STAT — Same columns as V$ASM_DISKGROUP but without the discovery scan overhead; prefer for high-frequency monitoring

Version Notes

• Oracle 10g R1: View introduced; TYPE values limited to EXTERN, NORMAL, HIGH
• Oracle 10g R2: VOTING_FILES column added to identify Grid Infrastructure quorum disks
• Oracle 11g R2: Flex ASM introduced as a Grid Infrastructure option; TYPE column can show FLEX in extended Grid configurations
• Oracle 12c: USABLE_FILE_MB calculation improved to account for extended ASM features; CON_ID column added for CDB environments
• Oracle 18c / 19c: FLEX diskgroup redundancy fully supported; REQUIRED_MIRROR_FREE_MB reflects variable mirror factor per file in FLEX diskgroups
• Oracle 21c / 23ai: No structural column changes; FLEX diskgroups with Oracle Automatic Storage Management Cluster File System (ACFS) show additional metadata in companion views