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
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24237320/viewspace-1059508/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24237320/viewspace-1059508/