表空間是 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 表空間保留時間、擴展區狀態、活躍交易統計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;
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;
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);
DBMS_SCHEDULER 定時執行,結果寫入巡檢歷史表。Segment Advisor 識別高碎片表空間,規劃 ALTER TABLESPACE ... COALESCE 或 Online Move 重組。