“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!
