select b.tablespace_name 表空间,
c.owner 用户,
c.segment_name 表名,
b.file_name 物理文件名,
sum(nvl(b.bytes, 0)) / 1024 / 1024 总共大小M,
round((sum(nvl(b.bytes, 0)) - sum(nvl(a.bytes, 0))) / 1024 / 1024, 2) 已经使用M,
round(sum(nvl(a.bytes, 0)) / 1024 / 1024, 2) 剩余M,
round(sum(c.bytes) / 1021 / 1024, 2) 该用户使用M
from (select tablespace_name, file_id, sum(bytes) bytes
from dba_free_space
group by tablespace_name, file_id) a,
dba_data_files b,
(select owner, segment_name, file_id, sum(bytes) bytes
from dba_extents
where owner = 'owner name'
group by owner, segment_name, file_id) c
where b.file_id = a.file_id
and b.file_id = c.file_id
group by b.tablespace_name,
b.file_name,
b.file_id,
c.owner,
c.segment_name
order by b.tablespace_name, c.owner, c.segment_name, b.file_name;
