select /*+ ordered use_hash(a,b,c) */
a.file_id as "文件id",
a.file_name as "文件名",
a.tablespace_name as "表空间名",
a.filesize as "文件大小",
b.freesize as "剩余空间",
(a.filesize - b.freesize) as "已用空间",
c.hwmsize as "高水位大小"
from (select tablespace_name,file_id, file_name, round(bytes / 1024 / 1024) filesize
from dba_data_files) a,
(select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize
from dba_free_space dfs
group by file_id) b,
(select file_id, round(max(block_id) * 8 / 1024) HWMsize
from dba_extents
group by file_id) c
where a.file_id = b.file_id
and a.file_id = c.file_id
order by b.freesize desc
sys用户执行,在pl/sql下
a.file_id as "文件id",
a.file_name as "文件名",
a.tablespace_name as "表空间名",
a.filesize as "文件大小",
b.freesize as "剩余空间",
(a.filesize - b.freesize) as "已用空间",
c.hwmsize as "高水位大小"
from (select tablespace_name,file_id, file_name, round(bytes / 1024 / 1024) filesize
from dba_data_files) a,
(select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize
from dba_free_space dfs
group by file_id) b,
(select file_id, round(max(block_id) * 8 / 1024) HWMsize
from dba_extents
group by file_id) c
where a.file_id = b.file_id
and a.file_id = c.file_id
order by b.freesize desc
sys用户执行,在pl/sql下