ASM 디렉토리 별 사용량 및 크기 확인 Query
set linesize window
set pagesize 100
col file_type format a20
col path format a50
col "USED%" for 990.9
define dirlv=2
select
file_type,
count(*) files,
round(sum(space)/1024/1024) mbytes,
round(sum(space)/1024/1024/dgmsize*100, 1) "USED%",
path
from
( select
concat('+'||gname, replace(sys_connect_by_path(case when level < &dirlv then '/'||aname else '' end, '|'), '|', null)) path,
file_type,
space,
gname,
dgmsize
from
( select b.name gname, a.name aname,
a.reference_index rindex, a.parent_index pindex,
c.type file_type, c.space,
a.system_created, a.alias_directory,
b.total_mb dgmsize
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(+)
)
where
connect_by_isleaf = 1 and alias_directory = 'N'
and not (system_created = 'N' and alias_directory = 'N')
start with (mod(pindex, power(2, 24))) = 0
connect by prior rindex = pindex
)
group by file_type, path, gname, dgmsize
order by gname, mbytes desc
;