运维常用查询表空间使用情况及利用率语句(按照使用率降序排列):
select d.tablespace_name "表空间名字",
d.status "状态",
round((a.bytes/1024/1024),2) as "总大小(M)",
round(((a.bytes-decode(f.bytes,null,0,f.bytes))/1024/1024),2) as "已使用(M)",
round((decode(f.bytes,null,0,f.bytes)/1024/1024),2) as "剩余(M)",
round(((a.bytes-decode(f.bytes, null,0,f.bytes))/1024/1024)/(a.bytes/1024/1024)*100) as "利用率(%)"
from sys.dba_tablespaces d, SYS.SM$TS_AVAIL a, SYS.SM$TS_FREE f
where d.tablespace_name = a.tablespace_name
and f.tablespace_name(+) = d.tablespace_name
order by 6 desc;
select d.tablespace_name "表空间名字",
d.status "状态",
round((a.bytes/1024/1024),2) as "总大小(M)",
round(((a.bytes-decode(f.bytes,null,0,f.bytes))/1024/1024),2) as "已使用(M)",
round((decode(f.bytes,null,0,f.bytes)/1024/1024),2) as "剩余(M)",
round(((a.bytes-decode(f.bytes, null,0,f.bytes))/1024/1024)/(a.bytes/1024/1024)*100) as "利用率(%)"
from sys.dba_tablespaces d, SYS.SM$TS_AVAIL a, SYS.SM$TS_FREE f
where d.tablespace_name = a.tablespace_name
and f.tablespace_name(+) = d.tablespace_name
order by 6 desc;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28507395/viewspace-1370587/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28507395/viewspace-1370587/