Query to find the size of Databases on an ASM disk group


Assupmtion: All datafiles/redolog/temp files are on ASM.

Query:

column DATABASE format a25
col "GB" format 99,999.9
set pagesize 20
SELECT
disk_group_name
,SUBSTR(alias_path,2,INSTR(alias_path,'/',1,2)-2) Database
,ROUND(SUM(alloc_bytes)/1024/1024/1024,1) "GB"
,ROUND(SUM(alloc_bytes)/1024/1024,1) "MB"
FROM
(SELECT
SYS_CONNECT_BY_PATH(alias_name, '/') alias_path
,alloc_bytes, disk_group_name
FROM
(SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, f.space alloc_bytes
, f.type type
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a
USING (group_number, file_number)
JOIN v$asm_diskgroup g
USING (group_number)
)
WHERE type IS NOT NULL
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
)
GROUP BY disk_group_name, SUBSTR(alias_path,2,INSTR(alias_path,'/',1,2)-2)
ORDER BY 1;



Run as grid owner by connecting sqlplus '/as sysasm'

Sample Output:

DISK_GROUP_NAME     DATABASE                     GB         MB
------------------ ------------------------- --------- ----------
TEST_DG                   DEV1                 88.3      90440
TEST_DG                   DEV2                104.5     106982
TEST_DG                   TERP1              1,127.4    1154435


If you like please follow and comment