[SQL] Tablespace 사용량 알아보는 쿼리

이 스크립트는 자신의 크기(100%)의 60%를 넘는 데이터파일이나 템프파일을 갖고 있는 테이블스페이스를 보여줍니다.

그리고 90일동안의 증가율도 보여줌니다.

추후 데이터 증가 추의를 파악하는데 도움이 될 것 같습니다.

SELECT CONTENTS,
       tablespace_name,
       CEIL (SUM (used) / POWER (1024, 3)) USED_GB,
       CEIL (SUM (total) / POWER (1024, 3)) MAXSIZE_GB,
       TO_CHAR (100 * SUM (used) / SUM (total), '999') || ' %' Pct_Used,
       TO_CHAR (100 * ((SUM (NVL2 (creation, used, 0)))) / SUM (total), 'S99999') || '% in last 90 days' GROWTH 
  FROM (
        SELECT what,
               tablespace_name,
               file_name,
               bytes_used used,
               CASE WHEN autoextensible = 'NO' THEN bytes WHEN NVL (maxbytes, 0) < bytes THEN bytes ELSE maxbytes END total 
          FROM (
                SELECT 'tempfile' what,
                       file_id,
                       tablespace_name,
                       file_name,
                       relative_fno,
                       bytes,
                       autoextensible,
                       maxbytes,
                       bytes_used 
                  FROM V$temp_space_header s JOIN DBA_TEMP_FILES f USING (file_id, tablespace_name, relative_fno) 
                 WHERE status = 'AVAILABLE'
                UNION ALL
                 SELECT 'datafile' what,
                       file_id,
                       tablespace_name,
                       file_name,
                       relative_fno,
                       f.bytes,
                       autoextensible,
                       maxbytes,
                       f.bytes - NVL (s.bytes, 0) bytes_used 
                  FROM DBA_DATA_FILES f LEFT OUTER JOIN (
                        SELECT file_id,
                               tablespace_name,
                               relative_fno,
                               SUM (bytes) bytes 
                          FROM DBA_FREE_SPACE GROUP BY file_id,
                               tablespace_name,
                               relative_fno
                       ) s USING (file_id, tablespace_name, relative_fno) 
                 WHERE status = 'AVAILABLE'
               )gggggg
       ) JOIN DBA_TABLESPACES USING (tablespace_name) LEFT OUTER JOIN (
        SELECT name file_name,
               SYSDATE - creation_time creation 
          FROM v$datafile 
         WHERE creation_time > SYSDATE - 90
       ) USING (file_name) HAVING SUM (used) / SUM (total) > (60 / 100) GROUP BY CONTENTS,
       tablespace_name ORDER BY 3 DESC;