[ASM] 사용량 및 Tablespace 사용량 현황

Oracle ASM 할당량과 사용량 현황 정보 및 Tablespace 할당량과 사용량 현황 정보 한번에 보기

데이터 전용 공간의 사용량을 파악하기 위해서,

Undo 및 Temp 사용량은 100% 사용을 가정하여 할당량 포함했다.(Free & Used Size 산정시 100% 사용 가정)

 

SELECT SUM(DECODE(gubun,'ASM',tbs_alloc_sz)) AS "ASM_TOTAL"
     , SUM(DECODE(gubun,'ASM',tbs_free_sz))  AS "ASM_FREE"
     , SUM(DECODE(gubun,'ASM',used_size))    AS "ASM_USED"
     , SUM(DECODE(gubun,'ASM',used_per))     AS "ASM_USAGE(%)"
     , SUM(DECODE(gubun,'TBS',tbs_alloc_sz)) AS "TBS_TOTAL" -- UNDO, TEMP 할당량 포함
     , SUM(DECODE(gubun,'TBS',tbs_free_sz))  AS "TBS_FREE"  -- UNDO, TEMP 할당량 100% 사용 가정한, 데이터 전용 Free 공간
     , SUM(DECODE(gubun,'TBS',used_size))    AS "TBS_USED"  -- UNDO, TEMP 할당량 100% 사용 가정한, 데이터 사용율(%)
     , SUM(DECODE(gubun,'TBS',used_per))     AS "TBS_USAGE(%)"
  FROM ( SELECT 'TBS' AS gubun
              , ROUND(SUM(tbs_alloc),1) AS tbs_alloc_sz
              , ROUND(SUM(CASE WHEN (tbs_name LIKE 'UNDO%' OR tbs_name = 'TEMP') THEN NULL ELSE tbs_free END),1) AS tbs_free_sz
              , ROUND(SUM(CASE WHEN (tbs_name LIKE 'UNDO%' OR tbs_name = 'TEMP') THEN tbs_alloc ELSE data_used END),1) AS used_size
              , ROUND(AVG(CASE WHEN (tbs_name LIKE 'UNDO%' OR tbs_name = 'TEMP') THEN NULL ELSE data_used_per END),1) AS used_per
           FROM ( SELECT a.tablespace_name AS tbs_name
                       , SUM(NVL(b.bytes/1024/1024/1024,0)) AS tbs_alloc
                       , SUM(NVL(b.bytes-NVL(c.bytes,0),0)/1024/1024/1024) AS data_used
                       , SUM(NVL(c.bytes/1024/1024/1024,0)) AS tbs_free
                       , AVG(NVL((b.bytes-NVL(c.bytes,0))/b.bytes*100,0)) AS data_used_per
                    FROM sys.dba_tablespaces a
                       , ( SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) b
                       , ( SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) c
                   WHERE a.tablespace_name = b.tablespace_name(+) 
                     AND a.tablespace_name = c.tablespace_name(+) 
                     AND NOT (a.extent_management LIKE 'LOCAL' AND a.contents LIKE 'TEMPORARY') 
                   GROUP BY a.tablespace_name
                   UNION ALL 
                  SELECT 'TEMP' AS tbs_name
                       , SUM(NVL(b.bytes/1024/1024/1024,0)) AS tbs_alloc
                       , SUM(NVL(c.bytes,0)/1024/1024/1024) AS data_used
                       , SUM(NVL((b.bytes-NVL(c.bytes,0))/1024/1024/1024,0)) AS tbs_free
                       , AVG(NVL(c.bytes/b.bytes*100,0)) AS data_used_per
                    FROM  sys.dba_tablespaces a
                       , (SELECT tablespace_name,SUM(bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) b
                       , (SELECT tablespace_name,SUM(bytes_cached) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) c 
                   WHERE a.tablespace_name = b.tablespace_name(+) 
                     AND a.tablespace_name = c.tablespace_name(+) 
                     AND a.extent_management LIKE 'LOCAL' 
                     AND a.contents LIKE 'TEMPORARY'
                   GROUP BY a.tablespace_name )               
          UNION ALL
         SELECT 'ASM' 
              , ROUND(total_mb/1024/DECODE(type,'HIGH',3,'NORMAL',2,'EXTERN',1),1)
              , ROUND(free_mb/1024/DECODE(type,'HIGH',3,'NORMAL',2,'EXTERN',1),1) 
              , ROUND((total_mb/1024/DECODE(type,'HIGH',3,'NORMAL',2,'EXTERN',1))-(free_mb/1024 /DECODE(type,'HIGH',3,'NORMAL',2,'EXTERN',1)),1)
              , ROUND(100-(((free_mb/DECODE(type,'HIGH',3,'NORMAL',2,'EXTERN',1))/(total_mb/DECODE(type,'HIGH',3,'NORMAL',2,'EXTERN',1)))*100),1)
           FROM v$asm_diskgroup
          WHERE SUBSTR(name,1,INSTR(name,'_')-1) IN (SELECT name FROM v$database)
);

 

더 쉬운 방법도 있었을 텐데...