监控表空间使用情况的SQL语句
SELECT to_number(to_char(sysdate, 'yyyymmdd')) MON_DATE,
UPPER(F.TABLESPACE_NAME) TABLESPACE_NAME,
D.TOT_GROOTTE_MB || 'M' TOTAL_SIZE,
to_char((D.TOT_GROOTTE_MB - F.TOTAL_BYTES), 'fm9999999990.00') || 'M' USAGE_SIZE,
F.TOTAL_BYTES || 'M' FREE_SIZE,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
D.TOT_GROOTTE_MB * 100,
2),
'990.99') || '%' USAGEP,
F.MAX_BYTES || 'M' MAXBLOCK
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.dba_data_files DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY F.TABLESPACE_NAME
UPPER(F.TABLESPACE_NAME) TABLESPACE_NAME,
D.TOT_GROOTTE_MB || 'M' TOTAL_SIZE,
to_char((D.TOT_GROOTTE_MB - F.TOTAL_BYTES), 'fm9999999990.00') || 'M' USAGE_SIZE,
F.TOTAL_BYTES || 'M' FREE_SIZE,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
D.TOT_GROOTTE_MB * 100,
2),
'990.99') || '%' USAGEP,
F.MAX_BYTES || 'M' MAXBLOCK
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.dba_data_files DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY F.TABLESPACE_NAME
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17179887/viewspace-610528/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
常用SQL语句整理
下一篇：
oracle分区表总结(转)
请登录后发表评论
登录
全部评论
