top of page

Oracle ASM Queries

ASM Metadata allows you to manage ASM disks and Diskgroups smoothly. The fastest way to administer ASM is via sqlplus / as sysasm. Here are some of my favorite ASM queries that will help you quickly understand its configuration

Query to Find ASM Diskgroup Usage

To check ASM diskgroup size along with free space and used percentage

     ROUND(TOTAL_MB / 1024, 2) "SIZE_GB",
     ROUND(FREE_MB / 1024, 2) "AVAILABLE_GB",
     ROUND((total_mb - free_mb) / total_mb * 100, 2) AS "USED%"
FROM v$asm_diskgroup;

Query to Find ASM Disks Usage

To check ASM disks size along with free space and used percentage

SELECT AS "Disk Group", AS "Disk Name",
  ROUND(d.total_mb / 1024, 2) AS "SIZE_GB",
  ROUND(d.free_mb / 1024, 2) AS "AVAILABLE_GB",
  ROUND((d.total_mb - d.free_mb) / d.total_mb * 100, 2) AS "USED%"
FROM v$asm_disk d
JOIN v$asm_diskgroup dg ON (d.group_number = dg.group_number)

Query to Find Databases Using ASM

To check which database instances are connected to ASM instance

SELECT instance_name, db_name, status, software_version 
FROM v$asm_client;

Add Disk to ASM Diskgroup

As root user, create the new ASM disk on the specific partition

oracleasm createdisk DATA02 /dev/sdc1

Check the newly added disk and attach it the diskgroup

select name, path, mount_status, header_status from v$asm_disk;

alter diskgroup DATA add disk ‘/dev/oracleasm/disks/DATA02’ 
NAME DATA02 rebalance power 100;

Check rebalance status - If no output, then rebalance is completed

select * from v$asm_operation;

Check the newly added disk in ASM Diskgroup

ASM Disks Header and Mount Status

There are two important columns under V$ASM_DISK which are MOUNT_STATUS and HEADER_STATUS. These two columns are required when you add / remove ASM disks.


  • MISSING – Disk is known to be part of the ASM disk group, but no disk in the storage

  • CLOSED – Disk is present in the storage system but is not being accessed by ASM

  • OPENED – Disk is present in the storage system and is being accessed by ASM

  • CACHED – Disk is present in the storage system, and is part of a disk group being accessed by the ASM instance. This is the normal state for disks in an ASM.


  • UNKNOWN – ASM disk header has not been read

  • CANDIDATE – Can be used

  • INCOMPATIBLE – Version number in the disk header is not compatible with the ASM version

  • PROVISIONED – Disk is not part of a disk group and may be added to a disk group

  • MEMBER – Already member of a diskgroup

  • FORMER – Once used, can be re-used

  • CONFLICT – ASM disk was not mounted due to a conflict

Related Posts

Heading 2

Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

bottom of page