오라클 테이블스페이스 확인

오라클 테이블스페이스 가용공간 확인 SQL이다.

 

테이블스페이스별 가용공간

SELECT  ts.tablespace_name ts_name, sum(ts.total_space) total_size,
          round(sum(ts.total_space - fs.free_space),1) used_size,
          sum(round(fs.free_space,1)) free_size,
          round(sum(ts.total_space - fs.free_space)/sum(ts.total_space),3)*100 used_ratio
   from (select tablespace_name, sum(bytes)/1024/1024 free_space
         from dba_free_space group by tablespace_name) fs,
        (select tablespace_name, sum(bytes)/1024/1024 total_space
         from dba_data_files group by tablespace_name) ts
   where fs.tablespace_name=ts.tablespace_name
   group by ts.tablespace_name
   order by 5 desc;

 

 

파일별 사용 공간 조회

SELECT  b.tablespace_name "TABLESPACE_NAME", b.file_name "FILE_NAME",
        TO_CHAR((b.bytes / 1024 / 1024),'999,999,990') "TOTAL SIZE(MB)",
        TO_CHAR((((b.bytes - sum(nvl(a.bytes,0)))) / 1024 / 1024),'999,999,990') "USED(MB)",
        TO_CHAR(((sum(nvl(a.bytes,0))) / 1024 / 1024),'999,999,990') "FREE SIZE(MB)",
        TRUNC(((sum(nvl(a.bytes,0)) / (b.bytes)) * 100),2) "FREE %",
        SYSDATE "Check Time"
  FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
  WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_name, b.bytes
ORDER BY b.tablespace_name;