ASM DISK SPACE REPORT
set lines 255
col path for a35
col Diskgroup for a15
col DiskName for a25
col disk# for 999
col total_mb for 999,999,999
col free_mb for 999,999,999
compute sum of total_mb on DiskGroup
compute sum of free_mb on DiskGroup
break on DiskGroup skip 1 on report -
set pages 255
select a.name DiskGroup, b.disk_number Disk#, b.name DiskName,
b.total_mb, b.free_mb,
-- b.path,
b.header_status
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number
order by b.group_number, b.disk_number, b.name;
DISK GROUP USAGE
set lines 120
col "Redundancy" for a15
col "Diskgroup" for a12
select a.name "Diskgroup" , round(sum(b.total_mb)/1024,1) "Size GB",
round(sum(b.free_mb)/1024,1) "Free GB",
decode (a.type, 'EXTERN',round(sum(b.free_mb)/1024,1),'NORMAL',round(sum(b.free_mb)/1024/2,1),'HIGH',round(sum(b.free_mb)/1024/3,1)) "Usable GB",
decode (a.type, 'EXTERN',round(sum(b.free_mb)/1024/1024,1),'NORMAL',round(sum(b.free_mb)/1024/1024/2,1),'HIGH',round(sum(b.free_mb)/1024/1024/3,1)) "Usable TB",
round((sum(b.total_mb)-sum(b.free_mb))/sum(b.total_mb)*1000)/10 "Use%",
a.type "Redundancy"
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number
group by a.name, a.type order by 1;
GROUP BY USAGE
set pages 100
col database for a15
SELECT NVL(dbname, '-- TOTAL') database, round(SUM(space)/1024/1024) mb_used,
round(SUM(space) / AVG(total_mb * 1024 * 1024) * 100, 2) pct_used
FROM (
SELECT gname, file_type, space, aname, system_created, alias_directory,
regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname, total_mb
FROM (
SELECT system_created, alias_directory, file_type,space, level, gname, aname,
concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, total_mb
FROM (
SELECT b.name gname, b.total_mb, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.system_created, a.alias_directory,
c.type file_type, c.space
FROM v$asm_alias a
JOIN v$asm_diskgroup b ON a.group_number = b.group_number
LEFT JOIN v$asm_file c ON a.group_number = c.group_number
AND a.file_number = c.file_number
AND a.file_incarnation = c.incarnation
) START WITH (mod(pindex, power(2, 24))) = 0 AND rindex IN (
SELECT a.reference_index FROM v$asm_alias a, v$asm_diskgroup b
WHERE a.group_number = b.group_number
AND (mod(a.parent_index, power(2, 24))) = 0
) CONNECT BY prior rindex = pindex
) WHERE NOT file_type IS NULL AND system_created = 'Y' )
GROUP BY ROLLUP (dbname)
/
Oracle ASM USAGE
select name, total_mb, free_mb,
round(100*(total_mb-free_mb)/greatest(1,total_mb),0) as used_pct
from v$asm_diskgroup;
