oracle 表空間使用率監控

借用師傅的勞動成果，在這裡介紹下，可以通過以下方式友善地找出監控表空間使用率的SQL：

找了個測試庫，確定隻有一個使用者連接配接，利用TOAD檢視表空間的使用率，先重新整理share pool,再重新整理檢視表空間的使用率，此時，可以在share pool檢視剛執行SQL，如下：

SELECT TS.TABLESPACE_NAME 表空間名,

       TS.STATUS 狀态,

       TS.CONTENTS,

       TS.EXTENT_MANAGEMENT,

       SIZE_INFO.MEGS_ALLOC,

       SIZE_INFO.MEGS_FREE,

       SIZE_INFO.MEGS_USED,

       SIZE_INFO.PCT_FREE,

       SIZE_INFO.PCT_USED,

       Round(SIZE_INFO.MEGS_USED*100 / SIZE_INFO.MAX) used_of_max,  ---add by myself

       SIZE_INFO.MAX

  FROM (SELECT A.TABLESPACE_NAME,

               ROUND(A.BYTES_ALLOC / 1024 / 1024) MEGS_ALLOC,

               ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024) MEGS_FREE,

               ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024) MEGS_USED,

               ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100) PCT_FREE,

               100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100) PCT_USED,

               ROUND(A.MAXBYTES / 1048576) MAX

          FROM (SELECT F.TABLESPACE_NAME,

                       SUM(F.BYTES) BYTES_ALLOC,

                       SUM(DECODE(F.AUTOEXTENSIBLE, 'YES', F.MAXBYTES, 'NO', F.BYTES)) MAXBYTES

                  FROM DBA_DATA_FILES F

                 GROUP BY TABLESPACE_NAME) A,

               (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_FREE

                  FROM DBA_FREE_SPACE F

                 GROUP BY TABLESPACE_NAME) B

         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)

        UNION ALL

        SELECT H.TABLESPACE_NAME,

               ROUND(SUM(H.BYTES_FREE + H.BYTES_USED) / 1048576) MEGS_ALLOC,

               ROUND(SUM((H.BYTES_FREE + H.BYTES_USED) -

                         NVL(P.BYTES_USED, 0)) / 1048576) MEGS_FREE,

               ROUND(SUM(NVL(P.BYTES_USED, 0)) / 1048576) MEGS_USED,

               ROUND((SUM((H.BYTES_FREE + H.BYTES_USED) -

                          NVL(P.BYTES_USED, 0)) /

                     SUM(H.BYTES_USED + H.BYTES_FREE)) * 100) PCT_FREE,

               100 - ROUND((SUM((H.BYTES_FREE + H.BYTES_USED) -

                                NVL(P.BYTES_USED, 0)) /

                           SUM(H.BYTES_USED + H.BYTES_FREE)) * 100) PCT_USED,

               ROUND(SUM(F.MAXBYTES) / 1048576) MAX

          FROM SYS.V_$TEMP_SPACE_HEADER H,

               SYS.V_$TEMP_EXTENT_POOL  P,

               DBA_TEMP_FILES           F

         WHERE P.FILE_ID(+) = H.FILE_ID

           AND P.TABLESPACE_NAME(+) = H.TABLESPACE_NAME

           AND F.FILE_ID = H.FILE_ID

           AND F.TABLESPACE_NAME = H.TABLESPACE_NAME

         GROUP BY H.TABLESPACE_NAME) SIZE_INFO,

       SYS.DBA_TABLESPACES TS

 WHERE TS.TABLESPACE_NAME = SIZE_INFO.TABLESPACE_NAME

以上包括臨時表空間的監控，如果隻需監控永久表空間，則簡單改寫為：

set LINESIZE 100

col TABLESPACE_NAME format A20

select *

from 

(

SELECT A.TABLESPACE_NAME,

       ROUND(A.BYTES_ALLOC / 1024 / 1024) MEGS_ALLOC,               

       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024) MEGS_USED,

       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0))*100/A.MAXBYTES) used_of_max,

       ROUND((A.MAXBYTES - A.BYTES_ALLOC + NVL(B.BYTES_FREE, 0))/1048576) free_of_max,

       ROUND(A.MAXBYTES / 1048576) MAX

FROM (SELECT F.TABLESPACE_NAME,

             SUM(F.BYTES) BYTES_ALLOC,

             SUM(DECODE(F.AUTOEXTENSIBLE, 'YES', F.MAXBYTES, 'NO', F.BYTES)) MAXBYTES

        FROM DBA_DATA_FILES F

       GROUP BY TABLESPACE_NAME) A,

               (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_FREE

                  FROM DBA_FREE_SPACE F

                 GROUP BY TABLESPACE_NAME) B

         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)

         )size_info 

         where size_info.used_of_max > 80;

監控内容隻需檢視used_of_max、free_of_max，其分别是已使用空間占最大表空間百分比、剩餘可擴充表空間大小。(以上腳本是監控表空間使用率超過80%的表空間。)