- 09 Oct, 2025
- 0 Comments
- 2 Mins Read
Oracle ASM Queries
Monitor ASM Storage Effectively with ASM Disk Usage Query
Efficient storage management is a critical part of Oracle Database administration.
With the ASM Disk Usage Query, DBAs can quickly check how much space is used or free across ASM diskgroups and individual disks.
Query to Check ASM Diskgroup Usage
To monitor ASM diskgroups and check their total size, available space, and used percentage, run the following query:
SELECT NAME, STATE, TYPE, ROUND(TOTAL_MB / 1024, 2) AS "SIZE_GB", ROUND(FREE_MB / 1024, 2) AS "AVAILABLE_GB", ROUND((TOTAL_MB - FREE_MB) / TOTAL_MB * 100, 2) AS "USED%" FROM v$asm_diskgroup;
Query to Check ASM Disk Usage
To get detailed information about individual ASM disks—including their size, free space, and usage percentage—use the following SQL:
SELECT dg.name AS "Disk Group", d.name 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 ORDER BY dg.name, d.name;
Query to Identify Databases Connected to ASM
To find which database instances are currently using the ASM instance, execute:
SELECT instance_name, db_name, status, software_version FROM v$asm_client;
Adding a New Disk to ASM Diskgroup
To add a new ASM disk, follow these steps:
As root user, create the ASM disk for the specified partition:
Verify the newly added disk:
SELECT name, path, mount_status, header_status FROM v$asm_disk;
Add the new disk to your ASM diskgroup and initiate a rebalance:
ASM Disk Header and Mount Status
ASM Disk MOUNT_STATUS
| Status | Description |
|---|---|
| MISSING | Disk belongs to a diskgroup but is not found in storage. |
| CLOSED | Disk is present but not being accessed by ASM. |
| OPENED | Disk is available and accessed by ASM. |
| CACHED | Disk is part of an active diskgroup and managed by ASM (normal state). |
ASM Disk HEADER_STATUS
| Status | Description |
|---|---|
| UNKNOWN | Disk header not yet read by ASM. |
| CANDIDATE | Disk can be used in ASM. |
| INCOMPATIBLE | Disk version incompatible with ASM version. |
| PROVISIONED | Disk not part of any diskgroup but can be added. |
| MEMBER | Disk is already a member of a diskgroup. |
| FORMER | Disk was previously used and can be reused. |
| CONFLICT | Disk not mounted due to conflict. |
Conclusion
At Learnomate Technologies, we make sure you not only understand such cutting-edge features but also know how to implement them in real-world projects. Whether you’re a beginner looking to break into the database world or an experienced professional upgrading your skillset—we’ve got your back with the most practical, hands-on training in Oracle technologies.
Want to see how we teach? Head over to our YouTube channel for insights, tutorials, and tech breakdowns: 
www.youtube.com/@learnomate
To know more about our courses, offerings, and team: Visit our official website: www.learnomate.org
Let’s connect and talk tech! Follow me on LinkedIn for more updates, thoughts, and learning resources: https://www.linkedin.com/in/ankushthavali/
If you want to read more about different technologies, Check out our detailed blog posts here: https://learnomate.org/blogs/
Let’s keep learning, exploring, and growing together. Because staying curious is the first step to staying ahead.