表空間耗盡預警腳本：考量 Autoextend 避免誤報

背景

舊腳本未考量資料檔 AUTOEXTEND 與 MAXSIZE，導致誤報「表空間耗盡」。本腳本修正此問題，可嵌入 Cron 郵件/簡訊通知 DBA。

腳本設定

• 預警閾值：少於 20 個 Extent 觸發（可調整）
• 查詢 DBA_FREE_SPACE、DBA_DATA_FILES、DBA_SEGMENTS、DBA_TABLESPACES

SQL 腳本

TTITLE 'Tablespaces That Will Exhaust After Less Than 20 Extents | ____________________________________________'
COLUMN OWNER FORMAT A12
COLUMN TABLESPACE_NAME FORMAT A22
COLUMN SEGMENT_NAME FORMAT A30
COLUMN "SIZE(M)" FORMAT 999,999
COLUMN "COMMENTS" FORMAT A35
SET NUMFORMAT 999,999,999,999.99

SELECT MAX_SIZE.TABLESPACE_NAME,
       "SIZE"/(1024*1024) "SIZE(MB)",
       (MAX_SIZE.MAX_EXTEND - "SIZE") + FREE_SIZE.FREE "FREE",
       FREE_SIZE.BIGGEST,
       FREE_SIZE.SMALLEST,
       MAX_SIZE.MAX_EXTEND/(1024*1024) "MAX_SIZE(MB)",
       TRUNC((MAX_SIZE.MAX_EXTEND - "SIZE" + FREE) / SEG_MAX_NEXT.NEXT_EXTENT) "EXHAUST_AFTER"
FROM
  (SELECT TABLESPACE_NAME, SUM(BYTES) "FREE", MAX(BYTES) "BIGGEST", MIN(BYTES) "SMALLEST"
   FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE_SIZE,
  (SELECT TABLESPACE_NAME, SUM(BYTES) "SIZE" FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) PHY_SIZE,
  (SELECT TABLESPACE_NAME, MAX(NEXT_EXTENT) NEXT_EXTENT FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME) SEG_MAX_NEXT,
  (SELECT TS.TABLESPACE_NAME, SUM(TS_SIZE) MAX_EXTEND
   FROM DBA_TABLESPACES TS,
     (SELECT TABLESPACE_NAME, AUTOEXTENSIBLE, DECODE(MAXBYTES,0,BYTES,MAXBYTES) "TS_SIZE"
      FROM DBA_DATA_FILES) DF
   WHERE DF.TABLESPACE_NAME = TS.TABLESPACE_NAME
   GROUP BY TS.TABLESPACE_NAME) MAX_SIZE
WHERE MAX_SIZE.TABLESPACE_NAME = SEG_MAX_NEXT.TABLESPACE_NAME
  AND MAX_SIZE.TABLESPACE_NAME = PHY_SIZE.TABLESPACE_NAME
  AND MAX_SIZE.TABLESPACE_NAME = FREE_SIZE.TABLESPACE_NAME
  AND SEG_MAX_NEXT.NEXT_EXTENT * 20 > ((MAX_SIZE.MAX_EXTEND - "SIZE") + FREE_SIZE.FREE)
ORDER BY MAX_SIZE.TABLESPACE_NAME;

輸出範例

TABLESPACE_NAME      | SIZE(MB) | FREE                | BIGGEST       | SMALLEST      | MAX_SIZE(MB) | EXHAUST_AFTER
MAS_128M_IX05        | 39,040   | 2,147,483,648       | 134,217,728   | 134,217,728   | 40,960       | 16
MAS_128M_IX06        | 39,040   | 2,147,483,648       | 134,217,728   | 134,217,728   | 40,960       | 16
MAS_128M_IX07        | 39,040   | 2,147,483,648       | 134,217,728   | 134,217,728   | 40,960       | 16

此腳本正確計算含 Autoextend 的最大可用空間，避免誤報。