Oracle中查看表空间使用率的SQL脚本分享

/*Formattedon2012/5/3114:51:13(QP5v5.185.11230.41888)*/
SELECTD.TABLESPACE_NAME,
      SPACE||"M""SUM_SPACE(M)",
      BLOCKS"SUM_BLOCKS",
      SPACE-NVL(FREE_SPACE,0)||"M""USED_SPACE(M)",
      ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)||"%"
         "USED_RATE(%)",
      FREE_SPACE||"M""FREE_SPACE(M)"
 FROM( SELECTTABLESPACE_NAME,
                ROUND(SUM(BYTES)/(1024*1024),2)SPACE,
                SUM(BLOCKS)BLOCKS
           FROMDBA_DATA_FILES
       GROUPBYTABLESPACE_NAME)D,
      ( SELECTTABLESPACE_NAME,
                ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACE
           FROMDBA_FREE_SPACE
       GROUPBYTABLESPACE_NAME)F
 WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
UNIONALL                                                          --如果有临时表空间
SELECTD.TABLESPACE_NAME,
      SPACE||"M""SUM_SPACE(M)",
      BLOCKSSUM_BLOCKS,
      USED_SPACE||"M""USED_SPACE(M)",
      ROUND(NVL(USED_SPACE,0)/SPACE*100,2)||"%""USED_RATE(%)",
      NVL(FREE_SPACE,0)||"M""FREE_SPACE(M)"
 FROM( SELECTTABLESPACE_NAME,
                ROUND(SUM(BYTES)/(1024*1024),2)SPACE,
                SUM(BLOCKS)BLOCKS
           FROMDBA_TEMP_FILES
       GROUPBYTABLESPACE_NAME)D,
      ( SELECTTABLESPACE_NAME,
                ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,
                ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACE
           FROMV$TEMP_SPACE_HEADER
       GROUPBYTABLESPACE_NAME)F
 WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
ORDERBY1;