ASM Diskspace utilization categorized by Database/File names

SCOPE

Below script helps to identify the space utilization of Databases including the files such as Datafiles, Redologfiles, Archivelog files on ASM diskgroup categorized by Database names and their files.

This helps in periodic capacity planning.

Script

set pagesize 9999
set linesize 230

col Database form a20 HEADING 'Database Name'
col type form a20 HEADING 'File type'
column Allocated_GB FORMAT 999,999.99     HEADING 'Allocated (GB)'
column size_gb FORMAT 999,999.99     HEADING 'Size (GB)'

break on report on Database skip 1

compute sum label ""              of size_gb allocated_GB on Database
compute sum label "Grand Total: " of size_gb allocated_GB on report

SELECT
SUBSTR(alias_path,2,INSTR(alias_path,'/',1,2)-2) Database,
type
,ROUND(SUM(bytes)/1024/1024/1024,2) size_GB
,ROUND(SUM(alloc_bytes)/1024/1024/1024,2) Allocated_GB
FROM
(SELECT
SYS_CONNECT_BY_PATH(alias_name, '/') alias_path
,alloc_bytes
,bytes
,type
FROM
(SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, f.space alloc_bytes
, f.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
and type like '%&FILETYPE%'
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex
)
GROUP BY SUBSTR(alias_path,2,INSTR(alias_path,'/',1,2)-2), type
ORDER BY 1
/