Oracle 資料表空間使用率監控

可以通過以下方式方便地找出監控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%的資料表空間。)

Oracle Undo 鏡像資料探究

Oracle 復原(ROLLBACK)和撤銷(Undo)

Undo 資料表空間損壞導致無法open

Undo資料表空間失敗的處理方法

Oracle Undo資料表空間重建與恢複