ASM Diskspace utilization categorized by Database/File names

 

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

/