【Oracle】DBA_FREE_SPACE で表領域の空き容量を調べる方法｜列の意味・断片化検出・空きエクステント分析まで解説

-- 表領域ごとの空き容量合計
SELECT tablespace_name,
       COUNT(*) AS free_extents,
       ROUND(SUM(bytes)/1024/1024, 1) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY free_mb;

SELECT
    df.tablespace_name,
    ROUND(df.total_mb, 1) AS total_mb,
    ROUND(df.total_mb - NVL(fs.free_mb, 0), 1) AS used_mb,
    ROUND(NVL(fs.free_mb, 0), 1) AS free_mb,
    ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 1) AS used_pct
FROM
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
     FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
     FROM dba_free_space GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY used_pct DESC;

-- データファイルごとの空き容量
SELECT df.tablespace_name, df.file_name,
       ROUND(df.bytes/1024/1024) AS file_mb,
       ROUND(NVL(fs.free_bytes, 0)/1024/1024, 1) AS free_mb,
       ROUND((df.bytes - NVL(fs.free_bytes, 0)) / df.bytes * 100, 1) AS used_pct
FROM dba_data_files df
LEFT JOIN (
    SELECT file_id, SUM(bytes) AS free_bytes
    FROM dba_free_space GROUP BY file_id
) fs ON df.file_id = fs.file_id
ORDER BY df.tablespace_name, df.file_name;

-- 表領域ごとの空きエクステント数と平均サイズ
SELECT tablespace_name,
       COUNT(*) AS free_extent_count,
       ROUND(SUM(bytes)/1024/1024, 1) AS total_free_mb,
       ROUND(AVG(bytes)/1024/1024, 2) AS avg_free_mb,
       ROUND(MAX(bytes)/1024/1024, 1) AS max_free_mb,
       ROUND(MIN(bytes)/1024/1024, 2) AS min_free_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY free_extent_count DESC;

-- テーブル拡張に必要な最低限の連続空きがあるか確認
SELECT tablespace_name,
       ROUND(MAX(bytes)/1024/1024, 1) AS max_contiguous_free_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY max_contiguous_free_mb;

-- ORA-01653 で「128 ブロック必要」と言われた場合
-- 128 * 8192（8KB ブロック）= 1MB の連続空きが必要
-- max_contiguous_free_mb が 1MB 以上あるか確認

-- 隣接する空きエクステントを結合して連続空き領域を拡大
ALTER TABLESPACE users COALESCE;

-- COALESCE 前後の比較
-- 実行前: free_extent_count=150, max_free=2MB
-- 実行後: free_extent_count=10,  max_free=200MB

-- テーブルを同じ表領域内で再配置（連続領域に再格納）
ALTER TABLE hr.employees MOVE;

-- MOVE 後はインデックスの再構築が必須
ALTER INDEX hr.idx_emp_dept REBUILD;

-- SHRINK SPACE: 行移動ありの圧縮（12c 以降推奨）
ALTER TABLE hr.employees ENABLE ROW MOVEMENT;
ALTER TABLE hr.employees SHRINK SPACE CASCADE;
-- CASCADE: テーブル + 全インデックスを同時に圧縮

-- DBA_TEMP_FREE_SPACE（11g 以降）
SELECT tablespace_name,
       ROUND(tablespace_size/1024/1024) AS total_mb,
       ROUND(free_space/1024/1024) AS free_mb
FROM dba_temp_free_space;

SELECT tablespace_name, status,
       ROUND(SUM(bytes)/1024/1024, 1) AS mb
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;
-- EXPIRED = 再利用可能（実質的な空き）

-- DBA ロールがなくても自分の表領域の空きを確認
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024, 1) AS free_mb
FROM user_free_space
GROUP BY tablespace_name;

-- 空き 500MB 未満の表領域を検出
SELECT df.tablespace_name,
       ROUND(NVL(fs.free_mb, 0), 1) AS free_mb,
       ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 1) AS used_pct
FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
      FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
           FROM dba_free_space GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
WHERE NVL(fs.free_mb, 0) < 500
ORDER BY free_mb;

-- 空きエクステント数が 50 以上 = 断片化の疑い
SELECT tablespace_name,
       COUNT(*) AS free_extents,
       ROUND(SUM(bytes)/1024/1024, 1) AS total_free_mb,
       ROUND(MAX(bytes)/1024/1024, 1) AS max_contiguous_mb
FROM dba_free_space
GROUP BY tablespace_name
HAVING COUNT(*) >= 50
ORDER BY free_extents DESC;

-- 断片化が激しい場合は COALESCE を実行
-- ALTER TABLESPACE 表領域名 COALESCE;

-- アラート対象の表領域を検出
SELECT tablespace_name, used_pct, free_mb
FROM (
    SELECT df.tablespace_name,
           ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 1) AS used_pct,
           ROUND(NVL(fs.free_mb, 0), 1) AS free_mb
    FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
          FROM dba_data_files GROUP BY tablespace_name) df
    LEFT JOIN (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
               FROM dba_free_space GROUP BY tablespace_name) fs
    ON df.tablespace_name = fs.tablespace_name
)
WHERE used_pct >= 80 OR free_mb < 500
ORDER BY used_pct DESC;

-- 特定の表領域の空きエクステントを確認（断片化調査）
SELECT file_id, block_id,
       ROUND(bytes/1024/1024, 2) AS free_mb,
       blocks
FROM dba_free_space
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC;

-- 上位 5 件が十分大きければ断片化は問題なし
-- 全件が小さい（数 KB）場合は断片化が深刻