오라클 테이블스페이스 사용량 조회

1. Redo Log Usage

col MEMBER format a40
SELECT l.group#, l.bytes/(1024*1024) AS "MEGA", l.status, f.member
FROM v$log l, v$logfile f
WHERE l.group# = f.group#;

 

2. DATA Tablespace Usage

SELECT A.TABLESPACE_NAME AS "TABLESPACE"
     , A.BYTES / 1024 / 1024  AS "TOTAL(MB)"
     , (A.BYTES - NVL(B.FREEBYTES,0)) / 1024 / 1024 AS "Used(MB)"
     , NVL(B.FREEBYTES,0) / 1024 / 1024 AS "Free(MB)"
     , ROUND(NVL(B.FREEBYTES,0)*100/A.BYTES,2) AS "Free(%)"
FROM (
      SELECT TABLESPACE_NAME
           , SUM(BYTES) BYTES
        FROM DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
      ) A
   , (
      SELECT TABLESPACE_NAME
           , SUM(NVL(BYTES,0)) FREEBYTES
        FROM DBA_FREE_SPACE
       GROUP BY TABLESPACE_NAME
      ) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5 ASC;

 

3. Temp Tablespace Usage

SELECT A.tablespace_name tablespace, D.mb_total,
 SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
 D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
 FROM v$sort_segment A,
 (
 SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
 FROM v$tablespace B, v$tempfile C
 WHERE B.ts#= C.ts#
 GROUP BY B.name, C.block_size
 ) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;