“Holy space usage, DBAtman!”
OK, So now you have a lot of databases on same DG, and don’t know how much of DG is being used by each database?
Here is something that may help you:
@asm_usage.sql
-- aka asm-db.sql set pages 350 timing on col gname form a10 col dbname form a10 col file_type form a16 break on gname skip 2 on dbname skip 1 compute sum label total_db of gb on dbname compute sum label total_diskg of gb on gname 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, aname, system_created, alias_directory FROM ( SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, system_created, alias_directory, file_type, space, level, gname, aname FROM ( SELECT b.name gname, 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, 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 /
And example of the output:
SQL> @asm_usage.sql GNAME DBNAME FILE_TYPE MB GB #FILES ---------- ---------- ---------------- ---------- ---------- ---------- DATA_EXE1 ASM PARAMETERFILE 8 0 1 ********** ---------- total_db 0 DBA CHANGETRACKING 24 0 1 CONTROLFILE 600 1 2 DATAFILE 198300 194 8 ONLINELOG 8352 8 8 PARAMETERFILE 8 0 1 TEMPFILE 5452 5 1 ********** ---------- total_db 208 DBB CHANGETRACKING 24 0 1 CONTROLFILE 984 1 2 ONLINELOG 8352 8 8 PARAMETERFILE 8 0 1 ********** ---------- total_db 9 DBC CHANGETRACKING 24 0 1 CONTROLFILE 96 0 1 DATAFILE 26564 26 9 ONLINELOG 32928 32 8 PARAMETERFILE 8 0 1 TEMPFILE 72 0 1 ********** ---------- total_db 58 DBD CHANGETRACKING 24 0 1 CONTROLFILE 192 0 1 DATAFILE 12800 13 9 ONLINELOG 32928 32 8 PARAMETERFILE 8 0 1 TEMPFILE 72 0 1 ********** ---------- total_db 45 DBE CHANGETRACKING 24 0 1 CONTROLFILE 492 0 1 DATAFILE 133220 130 5 ONLINELOG 65696 64 8 PARAMETERFILE 8 0 1 TEMPFILE 65548 64 1 ********** ---------- total_db 258 DB_UNKNOWN PARAMETERFILE 24 0 3 ********** ---------- total_db 0 DBX_EX CHANGETRACKING 104 0 1 CONTROLFILE 192 0 1 ONLINELOG 32928 32 8 PARAMETERFILE 8 0 1 TEMPFILE 65548 64 1 ********** ---------- total_db 96 DWMSP CHANGETRACKING 88 0 1 CONTROLFILE 192 0 1 DATAFILE 931752 910 12 DATAGUARDCONFIG 16 0 2 ONLINELOG 65856 64 16 PARAMETERFILE 8 0 1 TEMPFILE 131080 128 2 ********** ---------- total_db 1102 DWMSQ CHANGETRACKING 128 0 1 CONTROLFILE 192 0 2 ONLINELOG 32928 32 8 PARAMETERFILE 8 0 1 TEMPFILE 65548 64 1 ********** ---------- total_db 96 DBAB CHANGETRACKING 24 0 1 CONTROLFILE 96 0 1 ONLINELOG 32928 32 8 PARAMETERFILE 8 0 1 TEMPFILE 72 0 1 ********** ---------- total_db 32 DBXA CHANGETRACKING 540 1 1 CONTROLFILE 192 0 2 DATAFILE 1229068 1200 1 ONLINELOG 32928 32 8 PARAMETERFILE 8 0 1 TEMPFILE 131080 128 2 ********** ---------- total_db 1361 EXMP CHANGETRACKING 104 0 1 CONTROLFILE 96 0 1 DATAFILE 1123928 1098 9 ONLINELOG 32928 32 8 PARAMETERFILE 8 0 1 TEMPFILE 20500 20 1 ********** ---------- total_db 1150 [....] ********** ---------- total_disk 41362
Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!