【Oracle】容量が大きいテーブルを探す方法｜DBA_SEGMENTS・HWM・圧縮・パーティション別サイズ確認SQL完全ガイド

「Oracle のディスクがいっぱいに近い」「どのテーブルが容量を食っているか分からない」そんなとき、まず実行すべき SQL を体系的にまとめました。DBA_SEGMENTS を使ったテーブルサイズ上位の特定から、パーティション別集計・LOB セグメントの把握・HWM（高水位標）の確認まで、実務で即使えるクエリ集です。

前提知識：Oracle のセグメントとは

Oracle では、テーブルやインデックスはそれぞれ セグメント として表領域上に確保されます。DBA_SEGMENTS（または USER_SEGMENTS）ビューにセグメント名・種別・サイズが記録されており、これが「テーブルの容量を調べる」出発点になります。

スキーマ内で容量が大きいテーブル上位を取得

自スキーマのテーブルサイズ上位 20 件

SELECT
  segment_name                        AS table_name,
  ROUND(bytes / 1024 / 1024, 2)       AS size_mb,
  ROUND(bytes / 1024 / 1024 / 1024, 3) AS size_gb,
  blocks,
  extents
FROM user_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

特定スキーマのテーブルサイズ上位（DBA 権限が必要）

SELECT
  owner,
  segment_name                        AS table_name,
  ROUND(bytes / 1024 / 1024, 2)       AS size_mb,
  ROUND(bytes / 1024 / 1024 / 1024, 3) AS size_gb,
  tablespace_name
FROM dba_segments
WHERE owner       = 'HR'
  AND segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

テーブル本体＋インデックス＋LOB を合算した実質サイズ

テーブル本体だけでなく、そのインデックスや LOB セグメントも含めた 合計ディスク使用量 を把握するには、セグメント種別を横断して集計します。

-- インデックス・LOB も含めた完全合算（推奨）
SELECT
  owner,
  table_name,
  ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_mb
FROM (
  -- テーブル本体・パーティション
  SELECT owner, segment_name AS table_name, bytes
  FROM dba_segments
  WHERE segment_type IN ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
    AND owner = 'HR'
  UNION ALL
  -- インデックス
  SELECT i.owner, i.table_name, s.bytes
  FROM dba_indexes i
  JOIN dba_segments s
    ON s.owner = i.owner AND s.segment_name = i.index_name
  WHERE i.owner = 'HR'
  UNION ALL
  -- LOB セグメント
  SELECT l.owner, l.table_name, s.bytes
  FROM dba_lobs l
  JOIN dba_segments s
    ON s.owner = l.owner AND s.segment_name = l.segment_name
  WHERE l.owner = 'HR'
)
GROUP BY owner, table_name
ORDER BY total_mb DESC
FETCH FIRST 20 ROWS ONLY;

パーティションテーブルのパーティション別サイズ

パーティションテーブルでは、全体サイズより パーティション別の偏り を把握することが重要です。特定パーティションだけが肥大している場合、そのパーティションのみ TRUNCATE や EXCHANGE で対処できます。

-- パーティションテーブルの各パーティションサイズ
SELECT
  segment_name                         AS table_name,
  partition_name,
  ROUND(bytes / 1024 / 1024, 2)        AS size_mb,
  blocks,
  extents
FROM dba_segments
WHERE owner        = 'HR'
  AND segment_name = 'ORDERS'    -- テーブル名を指定
  AND segment_type = 'TABLE PARTITION'
ORDER BY bytes DESC;

-- パーティションテーブルを持つスキーマの全テーブルをパーティション別で集計
SELECT
  segment_name                         AS table_name,
  COUNT(*)                             AS partition_count,
  ROUND(SUM(bytes) / 1024 / 1024, 2)  AS total_mb,
  ROUND(MAX(bytes) / 1024 / 1024, 2)  AS max_partition_mb,
  ROUND(MIN(bytes) / 1024 / 1024, 2)  AS min_partition_mb
FROM dba_segments
WHERE owner       = 'HR'
  AND segment_type = 'TABLE PARTITION'
GROUP BY segment_name
ORDER BY total_mb DESC;

LOB セグメントが大きいテーブルを探す

CLOB/BLOB 列を持つテーブルでは、LOB データが別セグメントに格納されるため、テーブル本体のサイズだけ見ていると見落とします。

-- LOB セグメントサイズ上位（テーブル名・列名付き）
SELECT
  l.table_name,
  l.column_name,
  l.segment_name,
  ROUND(s.bytes / 1024 / 1024, 2)  AS lob_mb,
  s.tablespace_name
FROM dba_lobs l
JOIN dba_segments s
  ON s.owner = l.owner AND s.segment_name = l.segment_name
WHERE l.owner = 'HR'
ORDER BY s.bytes DESC;

表領域横断でディスク使用量が多いオブジェクトを探す

-- データベース全体でサイズ上位 30（DBA ロール必要）
SELECT
  owner,
  segment_name,
  segment_type,
  tablespace_name,
  ROUND(bytes / 1024 / 1024, 2)        AS size_mb,
  ROUND(bytes / 1024 / 1024 / 1024, 3) AS size_gb
FROM dba_segments
ORDER BY bytes DESC
FETCH FIRST 30 ROWS ONLY;

-- 表領域別・オーナー別の合計サイズ
SELECT
  tablespace_name,
  owner,
  ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS total_gb,
  COUNT(*)                                    AS segment_count
FROM dba_segments
GROUP BY tablespace_name, owner
ORDER BY total_gb DESC;

HWM（高水位標）と実データの乖離を確認する

DELETE を繰り返したテーブルは、HWM（High Water Mark）以下の空きブロックが多数残った状態になり、セグメントサイズが実データ量より大きく見えます。

-- テーブルの割当ブロック数 vs 実使用ブロック数の比較
SELECT
  t.table_name,
  t.num_rows,
  t.blocks                              AS used_blocks,      -- 実データが入ったブロック
  t.empty_blocks,                                            -- HWM 以下の空きブロック
  s.blocks                              AS allocated_blocks, -- 割当済み総ブロック数
  ROUND(t.blocks / NULLIF(s.blocks, 0) * 100, 1) AS usage_pct,
  ROUND(s.bytes / 1024 / 1024, 2)       AS segment_mb
FROM user_tables t
JOIN user_segments s ON s.segment_name = t.table_name
WHERE s.segment_type = 'TABLE'
  AND s.blocks > 1000   -- ある程度の大きさのテーブルに限定
ORDER BY (1 - COALESCE(t.blocks, 0) / NULLIF(s.blocks, 0)) DESC  -- 無駄率が高い順
FETCH FIRST 20 ROWS ONLY;

サイズ削減の方向性（確認後の次のアクション）

よくある質問（FAQ）

まとめ

まず DBA_SEGMENTS でサイズ上位テーブルを特定し、次にインデックス・LOB を含む実質サイズを集計、HWM の乖離を確認するという 3 ステップで進めると効率的です。サイズ削減に進む前に必ず対象テーブルのバックアップと、SHRINK/MOVE の影響範囲（インデックス・制約）を確認してください。