ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 贴一个求表空间的sql 语句

贴一个求表空间的sql 语句

原创 Linux操作系统 作者:lnwxzyp 时间:2011-05-13 19:58:26 0 删除 编辑
    这段时间经常发现用以前的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博客 ” ,链接:http://blog.itpub.net/12216142/viewspace-695245/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-04-25

  • 博文量
    129
  • 访问量
    683625