by tablespace(使用单位G):
SELECT a.tablespace_name,(all_size-b.unuse_size) use_size,b.unuse_size,a.all_size,
round((a.all_size-b.unuse_size)/a.all_size,2) usage
FROM (
SELECT tablespace_name,round(sum(bytes)/1024/1024/1024,2) all_size FROM dba_data_files group by tablespace_name) a,
(SELECT tablespace_name,round(sum(bytes)/1024/1024/1024,2) unuse_size FROM dba_free_space group by tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
by datafile(使用单位G):
SELECT a.tablespace_name,a.file_name,(a.all_size-nvl(b.unuse_size,0)) use_size,nvl(b.unuse_size,0),
a.all_size,round((a.all_size-nvl(b.unuse_size,0))/a.all_size,2) usage,a.autoextensible FROM
(SELECT tablespace_name,file_id,file_name,round(bytes/1024/1024/1024,4) all_size,autoextensible
FROM dba_data_files) a,
(SELECT file_id,round(sum(bytes)/1024/1024/1024,2) unuse_size FROM dba_free_space group by file_id)b
WHERE a.file_id=b.file_id(+)
order by a.tablespace_name,file_name