通过dba_data_files 与dba_free_space 按照tablespace_name分组统计,然后进行关联即可
select c.tablespace_name "表空间",
c.total "表空间总大小(M)",
c.total - d.free "已使用大小(M)",
nvl(d.free, 0) "空闲大小(M)",
round((c.total - d.free) / c.total, 4) * 100 || '%' "使用占比(%)"
from (select tablespace_name, round(sum(a.bytes) / (1024 * 1024), 2) total
from dba_data_files a
group by a.tablespace_name) c
left join (select b.tablespace_name,
round(sum(b.bytes) / (1024 * 1024), 2) free
from dba_free_space b
group by b.tablespace_name) d on c.tablespace_name =
d.tablespace_name
/* where c.tablespace_name like '%UNDOTBS%' 查询指定表空间使用情况*/
order by 4 desc
c.total "表空间总大小(M)",
c.total - d.free "已使用大小(M)",
nvl(d.free, 0) "空闲大小(M)",
round((c.total - d.free) / c.total, 4) * 100 || '%' "使用占比(%)"
from (select tablespace_name, round(sum(a.bytes) / (1024 * 1024), 2) total
from dba_data_files a
group by a.tablespace_name) c
left join (select b.tablespace_name,
round(sum(b.bytes) / (1024 * 1024), 2) free
from dba_free_space b
group by b.tablespace_name) d on c.tablespace_name =
d.tablespace_name
/* where c.tablespace_name like '%UNDOTBS%' 查询指定表空间使用情况*/
order by 4 desc