Oracle DB File/Tablespace Size Monitor

DB File/Tablespace Size Monitor

標籤： Oracle

查看每個File的使用率
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES/1024/1024/1024 as USED_G,D.MAXBYTES/1024/1024/1024 AS MAX_G,D.STATUS,(d.bytes/d.maxbytes)*100 AS UseRate
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME and d.maxbytes!=0 ORDER BY UseRate desc

查看Tablespace的平均使用率及剩餘空間
select t.tablespace_name ,avg(d.bytes/d.maxbytes)*100 as Used_Rate,sum(d.maxbytes)/1024/1024/1024 as max_g,sum(d.bytes)/1024/1024/1024 as Used_g,
       (sum(d.maxbytes)-sum(d.bytes))/1024/1024/1024 as free_space_g
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME and d.maxbytes!=0 group by t.tablespace_name
ORDER BY Free_space_g