Занятость в группах:
select name,total_mb,free_mb,state from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB STATE ------------------------------ ---------- ---------- ----------- DATA 7809760 520 MOUNTED
Какие каталоги и сколько потребляют места:
col dbname format a15
set line 512
SELECT gname, dbname, file_type, round(SUM(space)/1024/1024) mb,
round(SUM(space)/1024/1024/1024) gb, COUNT(*) "#FILES"
FROM ( SELECT gname, regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname, file_type, space
FROM ( SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, system_created, file_type, space, gname
FROM ( SELECT b.name gname, a.parent_index pindex, a.name aname, a.reference_index rindex , a.system_created, c.type file_type, c.space
FROM v$asm_alias a, v$asm_diskgroup b, v$asm_file c
WHERE
a.group_number = b.group_number
AND 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 gname, dbname, file_type
ORDER BY gname, dbname, file_type
/
GNAME DBNAME FILE_TYPE MB GB #FILES ------------------------------ --------------- ---------------------------------------------------------------- ---------- ---------- ---------- DATA ASM ASMPARAMETERFILE 4 0 1 DATA ASM PASSWORD 0 0 1 DATA SOMEDB CONTROLFILE 96 0 2 DATA SOMEDB DATAFILE 7466172 7291 763 DATA SOMEDB ONLINELOG 32832 32 8 DATA SOMEDB PARAMETERFILE 4 0 1 DATA SOMEDB TEMPFILE 98704 96 6 DATA SOMEDB2 CONTROLFILE 32 0 1 DATA SOMEDB2 DATAFILE 201116 196 17 DATA SOMEDB2 ONLINELOG 612 1 3 DATA SOMEDB2 PARAMETERFILE 4 0 1 GNAME DBNAME FILE_TYPE MB GB #FILES ------------------------------ --------------- ---------------------------------------------------------------- ---------- ---------- ---------- DATA SOMEDB2 TEMPFILE 964 1 1 DATA SOMEDB3 CONTROLFILE 32 0 1 DATA SOMEDB3 DATAFILE 7776 8 13 DATA SOMEDB3 ONLINELOG 612 1 3 DATA SOMEDB3 PARAMETERFILE 4 0 1 DATA SOMEDB3 TEMPFILE 92 0 1 17 rows selected.