Oracle 查詢數據文件包含哪些段對象：DBA_EXTENTS 實戰 SQL

Oracle 查詢數據文件包含哪些段對象：DBA_EXTENTS 實戰 SQL

DBA 常需要知道某個具體 Data File (數據文件) 裡存放了哪些 Table、Index、LOB 等段，以及各段佔用空間大小，以便規劃表空間擴容、移動大對象、檢查碎片。關鍵在於 DBA_EXTENTS 視圖記錄了每個 Extent 所屬的 File ID、Segment、Size。

核心查詢 SQL

1. 指定 File ID 查詢

SELECT e.segment_type      AS segment_type,
       e.segment_name      AS segment_name,
       f.file_name         AS file_name,
       SUM(e.bytes)/1024/1024 AS segment_size_mb
FROM   dba_extents e
JOIN   dba_data_files f ON e.file_id = f.file_id
WHERE  f.file_id = 109
GROUP BY e.segment_type, e.segment_name, f.file_name
ORDER BY segment_size_mb DESC;

2. 指定文件名查詢 (支援綁定變數)

VAR v_file VARCHAR2(512)
EXEC :v_file := '/u01/app/oracle/oradata/ORCL/users01.dbf'

SELECT e.segment_type, e.segment_name, f.file_name,
       SUM(e.bytes)/1024/1024 AS size_mb
FROM   dba_extents e
JOIN   dba_data_files f ON e.file_id = f.file_id
WHERE  f.file_name = :v_file
GROUP BY e.segment_type, e.segment_name, f.file_name
ORDER BY size_mb DESC;

為什麼不用 DBA_SEGMENTS？

DBA_SEGMENTS 的 HEADER_FILE 僅記錄 段頭所在的文件。一個大表可能跨多個文件，若只看 HEADER_FILE 會漏掉該表在其他文件中的 Extents。DBA_EXTENTS 逐個 Extent 記錄 File ID，精準反映段在各文件的實際分佈。

錯誤寫法示範 (僅供對比)

-- 錯誤：只能看到段頭在該文件的段，漏掉跨文件的段
SELECT ds.segment_name, ds.segment_type, df.file_name, SUM(ds.bytes)/1024/1024
FROM dba_segments ds
JOIN dba_data_files df ON ds.header_file = df.file_id
WHERE df.file_id = 109
GROUP BY ds.segment_name, ds.segment_type, df.file_name;

進階：查詢表空間層面的文件-段分佈熱力圖

SELECT f.file_name,
       e.segment_type,
       COUNT(*) AS extent_count,
       SUM(e.bytes)/1024/1024 AS total_mb,
       ROUND(RATIO_TO_REPORT(SUM(e.bytes)) OVER (PARTITION BY f.file_id) * 100, 2) AS pct_in_file
FROM   dba_extents e
JOIN   dba_data_files f ON e.file_id = f.file_id
WHERE  f.tablespace_name = 'USERS'
GROUP BY f.file_id, f.file_name, e.segment_type
ORDER BY f.file_id, total_mb DESC;

輸出欄位說明

應用場景

• 🔍 碎片分析：某文件被大量小 Extent 佔用，考慮 ALTER TABLE ... MOVE 或 SHRINK SPACE。
• 📦 大對象定位：找出佔用最大的 LOB 段，規劃移至專用表空間/ASM 磁碟組。
• ⚖️ 負載均衡：檢查表空間下各文件大小分佈，決定 ALTER DATABASE DATAFILE ... RESIZE 或新增文件策略。
• 🗑️ 刪除文件前檢查：確認文件內無段 (或僅有可移動段) 才能 DROP DATAFILE。

總結

查詢數據文件包含的段對象，**務必使用 DBA_EXTENTS 關聯 DBA_DATA_FILES**，而非 DBA_SEGMENTS。掌握此 SQL，可精準掌控表空間內部空間分佈，支撐容量規劃、效能調優、故障排查等核心 DBA 作業。