表空间使用率脚本

最新推荐文章于 2021-10-26 13:24:00 发布

东方-phantom

最新推荐文章于 2021-10-26 13:24:00 发布

阅读量351

点赞数

分类专栏： cronjob 监控脚本

本文链接：https://blog.csdn.net/hkyw000/article/details/51263222

版权

cronjob 监控脚本专栏收录该内容

8 篇文章 0 订阅

订阅专栏

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES " 已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比", F.TOTAL_BYTES "空闲空
间(M)",
F.MAX_BYTES "最大块(M)" 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 4 DESC;

version 2：

set pagesize 50
spool /data/backup/logs/Ora_TableSpace_Mon.log
prompt
prompt**************************************
prompt*    Show TABLESPACE information     *
prompt**************************************
SELECT
       d.tablespace_name TableSpace_Name,
       TO_CHAR(NVL(a.bytes, 0),'99,999,990.900') "Total_Size(M)",
       NVL((a.bytes - NVL(f.bytes, 0)), 0) "Used_Size(M)",
       trunc(f.bytes,0) "Free_Size(M)",
       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0))/a.bytes * 100, 0), '990.00') "Used%"
FROM
       dba_tablespaces d,
        (select tablespace_name,
                sum(bytes/1024/1024) bytes
         from
               dba_data_files
         group by
               tablespace_name) a,
        (select tablespace_name,
               sum(bytes/1024/1024) bytes
        from
               dba_free_space
        group by
               tablespace_name) f
WHERE
     d.tablespace_name = a.tablespace_name(+)
AND
     d.tablespace_name = f.tablespace_name(+);
spool off;
~

最终版本：

select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1048576), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1048576), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1048576), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 32768) / (1048576), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;