工作之余写了监控表空间使用率的sql语句和码友们分享下:
select a.tablespace_name "Tablespace",
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "Usage Pct(%)",
round(a.bytes_alloc / 1024 / 1024/1024,1) "Size(GB)",
round(nvl(b.bytes_free, 0) / 1024 / 1024/1024,1) "Free(GB)",
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024/1024,1) "Used(GB)",
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) "Free Pct(%)",
round(maxbytes/1048576/1024 ,1) "Max(GB)",
round(((bytes_alloc - NVL (b.bytes_free, 0) )/ maxbytes)*100) "Used Pct of Max(%)" ,
c.status, c.contents
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