select
b.tablespace_name 表空间名 ,
b.total /(10241024)“表空间大小(M)” ,
(b.total -a.free)/(10241024)“已使用(M)” ,
a.free /(1024*1024)“剩余(M)” ,
round((b.total -a.free)/total, 4)100 “使用率(%)” ,
round(b.allTotal/(10241024), 0) “扩展表空间大小(M)” ,
round((b.total -a.free)/b.allTotal, 4)*100 “扩展使用率(%)”,
b.fileCount 表空间文件数
from
(
select
tablespace_name,
sum(bytes) free
from
dba_free_space
group by
tablespace_name
) a,
(
select
tablespace_name ,
sum(bytes) total,
sum(decode(maxbytes, 0, bytes,null, bytes,maxbytes))allTotal,
count(file_name)fileCount
from
dba_data_files
group by
tablespace_name )b
where
a.tablespace_name=b.tablespace_name;