【Oracle】表領域の断片化を解消する方法｜ALTER TABLE MOVE・SHRINK SPACE・COALESCE・HWM の確認と再編成手順まで解説

-- セグメントサイズ（HWM まで）と実際の行数から無駄を推定
SELECT t.table_name,
       ROUND(s.bytes/1024/1024, 1) AS segment_mb,
       t.num_rows,
       t.avg_row_len,
       ROUND(t.num_rows * t.avg_row_len / 1024 / 1024, 1) AS est_data_mb,
       ROUND(s.bytes/1024/1024 - t.num_rows * t.avg_row_len / 1024 / 1024, 1) AS wasted_mb
FROM user_tables t
JOIN user_segments s ON t.table_name = s.segment_name AND s.segment_type = 'TABLE'
WHERE t.num_rows > 0
  AND s.bytes > t.num_rows * t.avg_row_len * 3  -- セグメントが推定の 3 倍以上
ORDER BY wasted_mb DESC;

-- ※ num_rows / avg_row_len は統計情報ベース
-- 正確にするには DBMS_STATS.GATHER_TABLE_STATS を先に実行

-- テーブルの空きブロック数を正確に取得
DECLARE
    v_unformatted_blocks NUMBER;
    v_unformatted_bytes  NUMBER;
    v_fs1_blocks NUMBER; v_fs1_bytes NUMBER;
    v_fs2_blocks NUMBER; v_fs2_bytes NUMBER;
    v_fs3_blocks NUMBER; v_fs3_bytes NUMBER;
    v_fs4_blocks NUMBER; v_fs4_bytes NUMBER;
    v_full_blocks NUMBER; v_full_bytes NUMBER;
BEGIN
    DBMS_SPACE.SPACE_USAGE(
        segment_owner     => USER,
        segment_name      => 'EMPLOYEES',
        segment_type      => 'TABLE',
        unformatted_blocks => v_unformatted_blocks,
        unformatted_bytes  => v_unformatted_bytes,
        fs1_blocks => v_fs1_blocks, fs1_bytes => v_fs1_bytes,
        fs2_blocks => v_fs2_blocks, fs2_bytes => v_fs2_bytes,
        fs3_blocks => v_fs3_blocks, fs3_bytes => v_fs3_bytes,
        fs4_blocks => v_fs4_blocks, fs4_bytes => v_fs4_bytes,
        full_blocks => v_full_blocks, full_bytes => v_full_bytes
    );
    DBMS_OUTPUT.PUT_LINE('Full blocks:        ' || v_full_blocks);
    DBMS_OUTPUT.PUT_LINE('0-25% free blocks:  ' || v_fs1_blocks);
    DBMS_OUTPUT.PUT_LINE('25-50% free blocks: ' || v_fs2_blocks);
    DBMS_OUTPUT.PUT_LINE('50-75% free blocks: ' || v_fs3_blocks);
    DBMS_OUTPUT.PUT_LINE('75-100% free blocks:' || v_fs4_blocks);
    DBMS_OUTPUT.PUT_LINE('Unformatted:        ' || v_unformatted_blocks);
END;
/
-- fs3/fs4/unformatted が多い = 断片化が激しい

-- 空きエクステント数と最大連続空き
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(*) >= 20  -- 20 個以上の断片 = 要注意
ORDER BY free_extents DESC;

-- 同じ表領域内で再配置
ALTER TABLE hr.employees MOVE;

-- 別の表領域に移動しながら再編成
ALTER TABLE hr.employees MOVE TABLESPACE new_data;

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

-- MOVE ONLINE: DML をブロックせずに再編成（12c 以降）
ALTER TABLE hr.employees MOVE ONLINE;

-- MOVE ONLINE ではインデックスも自動的にメンテナンスされる
-- → REBUILD 不要（12c 以降の大きなメリット）

-- テーブルの全インデックスを一括再構築
BEGIN
    FOR rec IN (
        SELECT index_name FROM user_indexes
        WHERE table_name = 'EMPLOYEES'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name || ' REBUILD';
    END LOOP;
END;
/

-- (1) 行移動を有効化（初回のみ）
ALTER TABLE hr.employees ENABLE ROW MOVEMENT;

-- (2) SHRINK SPACE 実行
ALTER TABLE hr.employees SHRINK SPACE;

-- CASCADE: テーブル + 全インデックスを同時に圧縮
ALTER TABLE hr.employees SHRINK SPACE CASCADE;

-- COMPACT: 行の移動だけ実行（HWM は後で下げる = 2 段階）
ALTER TABLE hr.employees SHRINK SPACE COMPACT;
-- 後日: HWM を下げる
ALTER TABLE hr.employees SHRINK SPACE;

-- 隣接する空きエクステントを結合
ALTER TABLESPACE users COALESCE;

-- COALESCE 前後の比較
SELECT tablespace_name, COUNT(*) AS free_extents,
       ROUND(MAX(bytes)/1024/1024, 1) AS max_contiguous_mb
FROM dba_free_space
WHERE tablespace_name = 'USERS'
GROUP BY tablespace_name;

-- ANALYZE INDEX で断片化を確認
ANALYZE INDEX hr.idx_emp_dept VALIDATE STRUCTURE;

-- INDEX_STATS で削除率を確認
SELECT name, height, lf_rows, del_lf_rows,
       ROUND(del_lf_rows / NULLIF(lf_rows, 0) * 100, 1) AS del_pct
FROM index_stats;
-- del_pct が 20% 以上なら REBUILD を検討

-- 基本
ALTER INDEX hr.idx_emp_dept REBUILD;

-- ONLINE: DML をブロックせずに再構築
ALTER INDEX hr.idx_emp_dept REBUILD ONLINE;

-- NOLOGGING + PARALLEL で高速再構築（メンテナンス時間帯）
ALTER INDEX hr.idx_emp_dept REBUILD NOLOGGING PARALLEL 4;
ALTER INDEX hr.idx_emp_dept LOGGING;
ALTER INDEX hr.idx_emp_dept NOPARALLEL;

-- LOB セグメントのサイズ確認
SELECT l.table_name, l.column_name, l.segment_name,
       ROUND(s.bytes/1024/1024, 1) AS lob_mb
FROM user_lobs l
JOIN user_segments s ON l.segment_name = s.segment_name
ORDER BY s.bytes DESC;

-- LOB の再編成: ALTER TABLE MOVE で LOB も一緒に移動
ALTER TABLE hr.documents MOVE
    LOB (content) STORE AS (TABLESPACE lob_data);

-- 12c 以降: MOVE ONLINE
ALTER TABLE hr.documents MOVE ONLINE
    LOB (content) STORE AS (TABLESPACE lob_data);

-- 再編成前後のセグメントサイズを比較
SELECT segment_name, segment_type,
       ROUND(bytes/1024/1024, 1) AS size_mb
FROM user_segments
WHERE segment_name = 'EMPLOYEES';

-- 再編成前: 500 MB → 再編成後: 150 MB = 350 MB 削減

-- 大量 DELETE でデータが 1/10 になったテーブルを再編成
ALTER TABLE hr.audit_log MOVE ONLINE;
-- インデックスも自動メンテナンス（12c MOVE ONLINE）

-- 統計再収集
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'AUDIT_LOG');

-- 断片化が激しいテーブルを一括 MOVE + REBUILD
BEGIN
    FOR rec IN (
        SELECT t.table_name
        FROM user_tables t
        JOIN user_segments s ON t.table_name = s.segment_name
        WHERE s.segment_type = 'TABLE'
          AND t.num_rows > 0
          AND s.bytes > t.num_rows * t.avg_row_len * 3
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.table_name || ' MOVE';
        FOR idx IN (
            SELECT index_name FROM user_indexes
            WHERE table_name = rec.table_name
        ) LOOP
            EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD';
        END LOOP;
    END LOOP;
END;
/

-- 業務時間中にオンラインで圧縮
ALTER TABLE hr.orders ENABLE ROW MOVEMENT;
ALTER TABLE hr.orders SHRINK SPACE CASCADE;

-- ORA-01653 が出た → COALESCE で応急処置
ALTER TABLESPACE users COALESCE;

-- 効果確認
SELECT COUNT(*) AS free_extents, ROUND(MAX(bytes)/1024/1024, 1) AS max_mb
FROM dba_free_space WHERE tablespace_name = 'USERS';