萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 表空間使用情況查詢與監控腳本大全

表空間是 Oracle 數據庫邏儲存結構的核心單元,監控其空間使用率、自動擴展狀態、碎片率與臨時表空間壓力,是 DBA 日常巡檢的重中之重。本文匯總生產環境驗證的實用查詢腳本,涵蓋永久表空間、UNDO 表空間、臨時表空間三大類型,並提供 HTML 報表生成範例。

核心查詢視圖說明

  • DBA_TABLESPACES:表空間屬性(大小管理方式、區塊管理、狀態)
  • DBA_DATA_FILES / DBA_TEMP_FILES:資料檔/臨時檔物理路徑、大小、AUTOEXTENSIBLE 狀態、MAXBYTES 上限
  • DBA_FREE_SPACE:永久表空間自由區塊統計
  • V$TEMP_SPACE_HEADER / V$SORT_SEGMENT:臨時表空間實際使用與排序區段活動
  • V$UNDOSTAT / DBA_UNDO_EXTENTS:UNDO 表空間保留時間、擴展區狀態、活躍交易統計

實用查詢腳本

1. 永久表空間使用率總覽(含自動擴展預測)

SELECT 
  t.tablespace_name,
  ROUND(t.total_mb, 2) total_mb,
  ROUND(t.used_mb, 2) used_mb,
  ROUND(t.free_mb, 2) free_mb,
  ROUND(t.used_pct, 2) used_pct,
  CASE WHEN t.autoextensible = 'YES' 
       THEN ROUND(t.max_mb - t.used_mb, 2) 
       ELSE ROUND(t.free_mb, 2) END AS available_mb,
  t.autoextensible
FROM (
  SELECT 
    d.tablespace_name,
    SUM(d.bytes)/1024/1024 total_mb,
    SUM(d.bytes)/1024/1024 - NVL(SUM(f.bytes)/1024/1024,0) used_mb,
    NVL(SUM(f.bytes)/1024/1024,0) free_mb,
    ROUND((1 - NVL(SUM(f.bytes)/SUM(d.bytes),0))*100, 2) used_pct,
    MAX(d.autoextensible) autoextensible,
    SUM(DECODE(d.autoextensible,'YES',d.maxbytes,d.bytes))/1024/1024 max_mb
  FROM dba_data_files d
  LEFT JOIN dba_free_space f ON d.file_id = f.file_id
  GROUP BY d.tablespace_name
) t
ORDER BY t.used_pct DESC;

2. 臨時表空間實時使用情況

SELECT 
  t.tablespace_name,
  ROUND(t.total_mb,2) total_mb,
  ROUND(t.used_mb,2) used_mb,
  ROUND(t.free_mb,2) free_mb,
  ROUND(t.used_pct,2) used_pct
FROM (
  SELECT 
    tf.tablespace_name,
    SUM(tf.bytes)/1024/1024 total_mb,
    SUM(sh.bytes_used)/1024/1024 used_mb,
    SUM(tf.bytes)/1024/1024 - SUM(sh.bytes_used)/1024/1024 free_mb,
    ROUND(SUM(sh.bytes_used)/SUM(tf.bytes)*100,2) used_pct
  FROM dba_temp_files tf
  JOIN v$temp_space_header sh ON tf.file_id = sh.file_id
  GROUP BY tf.tablespace_name
) t;

3. UNDO 表空間保留時間與活躍交易監控

SELECT 
  u.tablespace_name,
  ROUND(u.total_mb,2) total_mb,
  ROUND(u.used_mb,2) used_mb,
  ROUND(u.used_pct,2) used_pct,
  u.retention_guarantee,
  s.tuned_undoretention current_retention_sec,
  s.maxquerylen max_query_len_sec
FROM (
  SELECT 
    d.tablespace_name,
    SUM(d.bytes)/1024/1024 total_mb,
    SUM(d.bytes)/1024/1024 - NVL(SUM(f.bytes)/1024/1024,0) used_mb,
    ROUND((1 - NVL(SUM(f.bytes)/SUM(d.bytes),0))*100,2) used_pct,
    MAX(t.retention) retention_guarantee
  FROM dba_data_files d
  JOIN dba_tablespaces t ON d.tablespace_name = t.tablespace_name
  LEFT JOIN dba_free_space f ON d.file_id = f.file_id
  WHERE t.contents = 'UNDO'
  GROUP BY d.tablespace_name
) u
JOIN v$undostat s ON 1=1
WHERE s.end_time = (SELECT MAX(end_time) FROM v$undostat);

自動化巡檢建議

  1. 將上述查詢封裝為 PL/SQL 程序,配合 DBMS_SCHEDULER 定時執行,結果寫入巡檢歷史表。
  2. 設定閾值告警:永久表空間 > 85% 或可用空間 < 2GB、臨時表空間 > 90%、UNDO 保留時間 < 目標值時發送郵件/企業微信通知。
  3. 定期執行 Segment Advisor 識別高碎片表空間,規劃 ALTER TABLESPACE ... COALESCE 或 Online Move 重組。
copyright © 萬盛學電腦網 all rights reserved