set pages 1000 lines 132

col name form a30
col state form a15

prompt Disk groups
select name, state, type, total_mb, free_mb from v$asm_diskgroup;

col dg form a30
col db form a10
col total form a40
prompt Databases by disk group
select dg.name dg, a1.name db, to_char(SUM(vf.space)) total
  from V$ASM_ALIAS a1,  V$ASM_ALIAS a2  , V$ASM_ALIAS a3, V$ASM_FILE vf, V$ASM_DISKGROUP dg
  where a2.parent_index = a1.reference_index and a3.parent_index = a2.reference_index
  and (mod(a1.parent_index, 16777216) = 0)
  and a1.system_created = 'Y'
  and a1.alias_directory = 'Y'
  and a2.system_created = 'Y'
  and a2.alias_directory = 'Y'
  and a3.system_created = 'Y'
  and a3.alias_directory = 'N'
  and dg.group_number = a1.group_number
  and dg.group_number = vf.group_number
  and vf.file_number = a3.file_number
 and dg.state = 'MOUNTED'
group by dg.name, a1.name;