오라클 테이블스페이스별 용량/사용량 조회 쿼리

오라클 테이블스페이스별 용량/사용량 조회 쿼리

 

 

테이블스페이스별로 얼마만큼의 용량이 할당되어 있는지 얼마나 사용중인지 확인할수 있다.

 

 

 

MB단위

select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024,1) "TotalMB",
         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
         round(sum(a.sum1)/1024/1024,1) "FreeMB",
         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          group by tablespace_name) a
group by a.tablespace_name
order by tablespace;

 

 

GB단위

select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024/1024,1) "TotalGB",
         round(sum(a.total1)/1024/1024/1024,1)-round(sum(a.sum1)/1024/1024/1024,1) "UsedGB",
         round(sum(a.sum1)/1024/1024/1024,1) "FreeGB",
         round((round(sum(a.total1)/1024/1024/1024,1)-round(sum(a.sum1)/1024/1024/1024,1))/round(sum(a.total1)/1024/1024/1024,1)*100,2) "Used%"
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          group by tablespace_name) a
group by a.tablespace_name
order by tablespace;