set pagesize 200
column "表空间名称" format a18
column "占用率(%)" format a12
column "容量(M)" format a12
column "空闲(M)" format a12
column "使用(M)" format a12
column "最大容量(M)" format a15
column "可扩充容量(M)" format a15
column "采样时间" format a20
select a.tablespace_name "表空间名称",
to_char(100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)) "占用率(%)",
to_char(round(a.bytes_alloc/1024/1024,2)) "容量(M)",
to_char(round(nvl(b.bytes_free,0)/1024/1024,2)) "空闲(M)",
to_char(round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2)) "使用(M)",
to_char(round(maxbytes/1024/1024,2)) "最大容量(M)",
to_char(round(maxbytes/1024/1024,2)-a.bytes_alloc/1024/1024) "可扩充容量(M)",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by to_number("可扩充容量(M)") asc;
结果贴出来
|
