这段时间经常发现用以前的sql查出来的表空间情况不够准确，先是表空间占用达到100%的用以前的SQL语言查不出来，后来发现表空间状态为offline的也查不出来，也是自己在原来的语句的基础上写了一个，貌似效率不够高，好在每天也只是跑一次而已，贴出来供大家参考。
set linesize 300
set pagesize 999
col TABLESPACE_NAME format a30
col SPACE_STATUS format a10
col "SUM_SPACE(M)" format 999999999.9
col "USED_SPACE(M)" format 999999999.9
col SPACE_TYPE format a12
col "USED_RATE(%)" format a12
SELECT Z.*,V.STATUS SPACE_STATUS FROM (SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", TO_CHAR(ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2),'990.99')||'%' "USED_RATE(%)", 'Permanent' SPACE_TYPE
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)) Z,(SELECT A.TABLESPACE_NAME,MAX(B.STATUS) STATUS FROM DBA_DATA_FILES A,V$DATAFILE B WHERE A.FILE_ID = B.FILE# GROUP BY TABLESPACE_NAME) V
WHERE Z.TABLESPACE_NAME = V.TABLESPACE_NAME(+)
UNION ALL
SELECT Z.*,V.STATUS SPACE_STATUS FROM (SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",USED_SPACE "USED_SPACE(M)",TO_CHAR(ROUND(NVL(USED_SPACE,0)/SPACE*100,2),'990.99')||'%' "USED_RATE(%)" ,'Temporary' SPACE_TYPE
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)) Z,(SELECT A.TABLESPACE_NAME,MAX(B.STATUS) STATUS FROM DBA_DATA_FILES A,V$TEMPFILE B WHERE A.FILE_ID = B.FILE# GROUP BY TABLESPACE_NAME) V
WHERE Z.TABLESPACE_NAME = V.TABLESPACE_NAME(+);
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12216142/viewspace-695245/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
vm虚拟机启动 报511错误
请登录后发表评论
登录
全部评论
