いろいろ便利SQL

 

ASMディレクトリ毎の使用サイズ/使用率/ファイル数を確認するSQL

---

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
;

出力例）

FILE_TYPE                 FILES     MBYTES  USED% PATH
-------------------- ---------- ---------- ------ -----------------------
ONLINELOG                     4        816    8.0 +DATA/ORCL
DATAFILE                      3        696    6.8 +DATA/ORCL
TEMPFILE                      2        608    5.9 +DATA/ORCL
3 rows selected.