테이블 스페이스 용량조회

select  nvl(tablespace_name,'<<TOTAL>>') tablespace_name,
        sum(megs_alloc) megs_alloc,
        sum(megs_free)  megs_free,
        sum(megs_used)  megs_used,
        sum(Pct_Free)   Pct_Free,
        sum(Pct_used)   Pct_used,
        sum(Max)        Max_siz
from  (
select  a.tablespace_name tablespace_name ,
       trunc(round(a.bytes_alloc / 1024 / 1024, 2)) megs_alloc,
       trunc(round(nvl(b.bytes_free, 0) / 1024 / 1024, 2)) megs_free,
       trunc(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2)) megs_used,
       trunc(round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)) Pct_Free,
       trunc(100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)) Pct_used,
       trunc(round(maxbytes/1048576,2)) Max
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union
select  tablespace_name,
       trunc(round(sum(bytes_used + bytes_free) / 1048576, 2)),
       trunc(round(sum(bytes_free) / 1048576,2)),
       trunc(round(sum(bytes_used) / 1048576,2)),
       trunc(round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2)) Pct_Free,
       trunc(100 - round((sum(bytes_free) / sum(bytes_used + bytes_free)) * 100,2)) Pct_used,
       trunc(round(max(bytes_used + bytes_free) / 1048576, 2))
from   sys.v_$TEMP_SPACE_HEADER
group by tablespace_name
order by 1
)
group by rollup(tablespace_name)
order by 1

출처:http://www.dator.co.kr/dataworld/textyle/4370