Hi guys, I don't if I can do this in the forum but here I go....
I spent lot of my time looking for a query to obtain the total space free & used pct for an ASM Disk group and define a threshold. Many of them works if we have EXTERNAL redundancy, but, if we have HIGH or NORMAL, those queries don't work.
So, again, I spent lot of time to develop a query that could help me to obtain the free & used space in ASM disk group.
The following is the query:
select
name
,total_mb
,free_mb
,used_mb
,round(free_mb*100/total_mb) free_pct
,round(used_mb*100/total_mb) used_pct
,'['
|| rpad (nvl (rpad ('#', round (round(used_mb*100/total_mb) / 10), '#'), '_'),
10,
'_'
)
|| ']'
|| lpad (to_char (round(used_mb*100/total_mb)), 3, ' ')
|| '%' "used (%)"
from (
select
name
,round((total_mb-required_mirror_free_mb)/(decode(type,'HIGH',3,'NORMAL',2,1))) total_mb
,(usable_file_mb) free_mb
,round(((total_mb-required_mirror_free_mb)/(decode(type,'HIGH',3,'NORMAL',2,1)))-usable_file_mb) used_mb
from v$asm_diskgroup
)
;
Can you help me, please, running the query in your test environments to see if returns good results and to identify any possible bug in this query?
I did many tests in Oracle 11g with HIGH & EXTERNAL redundancy and it seems works fine.
Your feedback would be appreciated.
Thanks in advance,
Juan M