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;