ASM - Query to find the size of Databases on an ASM diskgroup

 

Query to find the size of Databases on an ASM diskgroup

Login as Grid user  -

sqlplus "/as sysasm"

Execute the 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;

OUTPUT

DISK_GROUP_NAME                DATABASE                         GB         MB

------------------------------ ------------------------- --------- ----------

DATA01                                     ASM                           592.6         606792

DATA01                                     REPOST                          3.5           3596

DATA01                                     STDB                            3.4           3456