📋Steps in this guide1/4

Oracle ASM Queries

Learn how to check ASM Diskgroup and ASM Disk usage Query in Oracle Database using SQL queries. Find total size, free space, and used percentage of ASM disks and diskgroups with easy-to-run queries.

oracle configurationintermediate

by OracleDba

18 views

Overview

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. To monitor ASM diskgroups and check their total size, available space, and used percentage, run the following query: To get detailed information about individual ASM disks —including their size, free space, and usage percentage—use the following SQL: To find which database instances are currently using the ASM instance, execute:

Code/Command (click line numbers to comment):

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;

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;

Section 2

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:

Code/Command (click line numbers to comment):

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

oracleasm createdisk DATA02 /dev/sdc1

SELECT name, path, mount_status, header_status 
FROM v$asm_disk;

Section 3

Add the new disk to your ASM diskgroup and initiate a rebalance: Check the rebalance status (if no rows are returned, the operation is complete): Confirm that the new disk is successfully added to the ASM diskgroup. 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.

Code/Command (click line numbers to comment):

ALTER DISKGROUP DATA ADD DISK '/dev/oracleasm/disks/DATA02' 
NAME DATA02 REBALANCE POWER 100;

SELECT * FROM v$asm_operation;

Section 4

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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!