【Oracle】LOBセグメントの削除と表領域の解放方法｜MOVE・SHRINK・PURGE完全解説

この記事で分かること

• LOBセグメント（CLOB/BLOB/NCLOB）の仕組みと命名規則
• DBA_SEGMENTS / DBA_LOBS でLOBサイズを確認するSQL
• DELETE後に領域が解放されない理由と正しい解放手順
• ALTER TABLE MOVE LOB によるLOBセグメントの再編成
• SHRINK SPACE によるオンライン縮小
• DROP TABLE / PURGE RECYCLEBIN での完全削除
• LOBセグメントを別表領域に移動するベストプラクティス
• データファイルのRESIZEで表領域を縮小する方法
• LOB肥大化の原因調査・バッチ削除・定期メンテナンス

ポイント：LOBセグメントが肥大化する主な理由は次の3つです。(1) LOBカラムに大量のデータが蓄積される。(2) DELETE しても LOBセグメントの領域は自動的に解放されない。(3) UPDATE で LOBデータを更新すると、旧データの領域がそのまま残る（BASICFILE LOB の場合）。

-- LOBセグメント名の構造
SYS_LOB0000012345C00006$$

-- 各部分の意味
SYS_LOB     : LOBセグメントであることを示すプレフィックス
0000012345  : テーブルのOBJECT_ID（DBA_OBJECTSで確認可能）
C           : CLOBの場合はC、BLOBの場合もC
00006       : カラムの内部ID（INTCOL#）
$$          : サフィックス

-- LOBセグメントの一覧とサイズを確認
SELECT segment_name,
       segment_type,
       tablespace_name,
       ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM   dba_segments
WHERE  segment_type = 'LOBSEGMENT'
ORDER BY bytes DESC;

SEGMENT_NAME                  SEGMENT_TYPE  TABLESPACE_NAME  SIZE_MB
--------------------------    ------------  ---------------  -------
SYS_LOB0000078523C00004$$     LOBSEGMENT    USERS            8,542.00
SYS_LOB0000065210C00003$$     LOBSEGMENT    USERS            3,128.50
SYS_LOB0000091045C00005$$     LOBSEGMENT    USERS            1,256.75

-- LOBセグメントとテーブル・カラムの対応を確認
SELECT l.owner,
       l.table_name,
       l.column_name,
       l.segment_name,
       l.tablespace_name,
       l.securefile
FROM   dba_lobs l
WHERE  l.owner = 'HR'
ORDER BY l.table_name, l.column_name;

OWNER  TABLE_NAME      COLUMN_NAME  SEGMENT_NAME                  TABLESPACE  SECUREFILE
-----  --------------  -----------  ----------------------------  ----------  ----------
HR     DOCUMENTS       FILE_DATA    SYS_LOB0000078523C00004$$     USERS       NO
HR     DOCUMENTS       THUMBNAIL    SYS_LOB0000078523C00005$$     USERS       NO
HR     LOG_ENTRIES     LOG_TEXT     SYS_LOB0000065210C00003$$     USERS       YES

-- LOBセグメントが大きいテーブルTOP10
SELECT *
FROM (
  SELECT l.owner,
         l.table_name,
         l.column_name,
         l.segment_name,
         l.securefile,
         ROUND(s.bytes / 1024 / 1024, 2) AS lob_size_mb,
         ROUND(s.bytes / 1024 / 1024 / 1024, 2) AS lob_size_gb
  FROM   dba_lobs l
  JOIN   dba_segments s
    ON   s.owner = l.owner
    AND  s.segment_name = l.segment_name
  ORDER BY s.bytes DESC
)
WHERE ROWNUM <= 10;

OWNER  TABLE_NAME       COLUMN_NAME  SEGMENT_NAME                  SEC  LOB_SIZE_MB  LOB_SIZE_GB
-----  ---------------  -----------  ----------------------------  ---  -----------  -----------
HR     DOCUMENTS        FILE_DATA    SYS_LOB0000078523C00004$$     NO     8,542.00         8.34
HR     AUDIT_LOG        LOG_DETAIL   SYS_LOB0000065210C00003$$     YES    3,128.50         3.06
APP    FILE_STORAGE     CONTENT      SYS_LOB0000091045C00005$$     NO     1,256.75         1.23
HR     DOCUMENTS        THUMBNAIL    SYS_LOB0000078523C00005$$     NO       845.25         0.83
APP    EMAIL_ARCHIVE    BODY_HTML    SYS_LOB0000088712C00006$$     YES      512.00         0.50

ポイント：このSQLを定期的に実行し、LOBセグメントのサイズ推移を監視することで、肥大化の早期検知が可能になります。特にSECUREFILEがNO（BasicFile）のLOBは領域管理が非効率なため、優先的に監視対象にしてください。

-- LOBデータを含む行を削除
DELETE FROM documents
WHERE created_date < TO_DATE('2023-01-01', 'YYYY-MM-DD');
COMMIT;

-- LOBカラムを空にする（行は残る）
UPDATE documents
SET    file_data = EMPTY_BLOB()
WHERE  doc_id = 100;
COMMIT;

注意：上記のDELETEやEMPTY_BLOB()を実行しても、DBA_SEGMENTSで確認するLOBセグメントのサイズはほとんど変わりません。削除されたデータの領域は「再利用可能な空き領域」としてLOBセグメント内部に残りますが、表領域やOSには返却されません。領域を実際に解放するには、次に説明するMOVEまたはSHRINKが必要です。

-- LOBセグメントを再編成（同じ表領域内で再作成）
ALTER TABLE documents
MOVE LOB (file_data) STORE AS (
  TABLESPACE users
);

-- 複数LOBカラムをまとめて再編成
ALTER TABLE documents
MOVE
  LOB (file_data) STORE AS (TABLESPACE users)
  LOB (thumbnail) STORE AS (TABLESPACE users);

注意：ALTER TABLE MOVEを実行すると、そのテーブルのすべてのインデックスが UNUSABLE（無効）状態になります。MOVE完了後に必ずインデックスを再構築してください（後述）。また、MOVE中はテーブルへのDML（INSERT/UPDATE/DELETE）がブロックされます。

-- 前提: 行移動を有効にする（未設定の場合）
ALTER TABLE documents ENABLE ROW MOVEMENT;

-- LOBセグメントを縮小
ALTER TABLE documents
MODIFY LOB (file_data) (
  SHRINK SPACE
);

-- テーブル + LOBセグメントをまとめて縮小
ALTER TABLE documents SHRINK SPACE CASCADE;

-- Step 1: MOVE LOB を実行
ALTER TABLE documents
MOVE LOB (file_data) STORE AS (
  TABLESPACE users
);

-- Step 2: 無効化されたインデックスを確認
SELECT index_name,
       index_type,
       status
FROM   user_indexes
WHERE  table_name = 'DOCUMENTS'
AND    status = 'UNUSABLE';

-- Step 3: インデックスを再構築
ALTER INDEX pk_documents REBUILD;
ALTER INDEX idx_documents_date REBUILD;

-- 無効インデックスを一括再構築
BEGIN
  FOR rec IN (
    SELECT index_name
    FROM   user_indexes
    WHERE  table_name = 'DOCUMENTS'
    AND    status = 'UNUSABLE'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER INDEX ' || rec.index_name || ' REBUILD';
    DBMS_OUTPUT.PUT_LINE('Rebuilt: ' || rec.index_name);
  END LOOP;
END;
/

-- テーブルをリサイクルビンに入れずに即削除
DROP TABLE documents PURGE;

-- 外部キー参照がある場合は CASCADE CONSTRAINTS を付ける
DROP TABLE documents CASCADE CONSTRAINTS PURGE;

ポイント：PURGEを付けた場合、FLASHBACK TABLE ... TO BEFORE DROPで復元することはできません。重要なデータがある場合は、DROP前に必ずバックアップを取得してください。

-- リサイクルビンの内容を確認
SELECT object_name,
       original_name,
       type,
       ts_name,
       ROUND(space * 8192 / 1024 / 1024, 2) AS size_mb
FROM   dba_recyclebin
WHERE  type IN ('TABLE', 'LOB', 'LOB INDEX')
ORDER BY droptime DESC;

OBJECT_NAME                    ORIGINAL_NAME                  TYPE       TS_NAME  SIZE_MB
-----------------------------  -----------------------------  ---------  -------  -------
BIN$abc123==                   DOCUMENTS                      TABLE      USERS      12.50
BIN$def456==                   SYS_LOB0000078523C00004$$      LOB        USERS   8,542.00
BIN$ghi789==                   SYS_IL0000078523C00004$$       LOB INDEX  USERS       8.25

-- 自スキーマのリサイクルビンを空にする
PURGE RECYCLEBIN;

-- 特定テーブルだけリサイクルビンから完全削除
PURGE TABLE documents;

-- DBA権限: 全ユーザーのリサイクルビンを空にする
PURGE DBA_RECYCLEBIN;

-- 特定表領域のリサイクルビンだけ空にする
PURGE TABLESPACE users;

注意：PURGE RECYCLEBINを実行すると、リサイクルビン内のすべてのオブジェクトが完全削除されます。FLASHBACK TABLEでの復元ができなくなるため、必要なテーブルがリサイクルビンに含まれていないか確認してから実行してください。

-- LOBセグメントを LOB_DATA 表領域に移動
ALTER TABLE documents
MOVE LOB (file_data) STORE AS (
  TABLESPACE lob_data
);

-- 移動後の確認
SELECT table_name,
       column_name,
       segment_name,
       tablespace_name
FROM   user_lobs
WHERE  table_name = 'DOCUMENTS';

TABLE_NAME  COLUMN_NAME  SEGMENT_NAME                  TABLESPACE_NAME
----------  -----------  ----------------------------  ---------------
DOCUMENTS   FILE_DATA    SYS_LOB0000078530C00004$$     LOB_DATA
DOCUMENTS   THUMBNAIL    SYS_LOB0000078523C00005$$     USERS

-- Step 1: LOB専用の表領域を作成
CREATE TABLESPACE lob_data
DATAFILE '/u01/oradata/mydb/lob_data01.dbf'
SIZE 10G
AUTOEXTEND ON NEXT 1G
MAXSIZE 50G;

-- Step 2: 既存テーブルのLOBを移動
ALTER TABLE documents
MOVE LOB (file_data) STORE AS (TABLESPACE lob_data);

-- Step 3: インデックスを再構築
ALTER INDEX pk_documents REBUILD;

-- Step 4: 新規テーブル作成時はLOBの格納先を指定
CREATE TABLE new_documents (
  doc_id    NUMBER PRIMARY KEY,
  doc_name  VARCHAR2(200),
  file_data BLOB
)
TABLESPACE users
LOB (file_data) STORE AS SECUREFILE (
  TABLESPACE lob_data
  DISABLE STORAGE IN ROW
);

-- 表領域ごとの使用量・空き容量を確認
SELECT t.tablespace_name,
       ROUND(t.total_mb, 2)  AS total_mb,
       ROUND(f.free_mb, 2)   AS free_mb,
       ROUND(t.total_mb - f.free_mb, 2) AS used_mb,
       ROUND(f.free_mb / t.total_mb * 100, 1) AS free_pct
FROM   (
  SELECT tablespace_name,
         SUM(bytes) / 1024 / 1024 AS total_mb
  FROM   dba_data_files
  GROUP BY tablespace_name
) t
LEFT JOIN (
  SELECT tablespace_name,
         SUM(bytes) / 1024 / 1024 AS free_mb
  FROM   dba_free_space
  GROUP BY tablespace_name
) f
ON t.tablespace_name = f.tablespace_name
ORDER BY free_pct;

TABLESPACE_NAME  TOTAL_MB    FREE_MB    USED_MB    FREE_PCT
---------------  ---------   --------   --------   --------
SYSTEM             800.00      45.00     755.00        5.6
USERS           20,480.00  12,800.00   7,680.00       62.5
LOB_DATA        10,240.00   9,500.00     740.00       92.8

-- データファイルの現在のサイズと縮小可能サイズを確認
SELECT file_name,
       ROUND(bytes / 1024 / 1024, 2) AS current_mb,
       autoextensible
FROM   dba_data_files
WHERE  tablespace_name = 'USERS';

-- データファイルを縮小
ALTER DATABASE DATAFILE
  '/u01/oradata/mydb/users01.dbf'
  RESIZE 8G;

注意：データファイルは、最後に使用されているブロックよりも小さくRESIZEすることはできません。空き領域がファイルの末尾にない場合、ORA-03297: file contains used data beyond requested RESIZE valueエラーが発生します。この場合は、先にMOVEで末尾のセグメントを移動してからRESIZEしてください。

-- 全データファイルの自動拡張設定を確認
SELECT tablespace_name,
       file_name,
       ROUND(bytes / 1024 / 1024, 2) AS current_mb,
       autoextensible,
       ROUND(maxbytes / 1024 / 1024, 2) AS max_mb,
       ROUND(increment_by * 8192 / 1024 / 1024, 2) AS increment_mb
FROM   dba_data_files
ORDER BY tablespace_name, file_name;

ポイント：自動拡張が有効（AUTOEXTENSIBLE = YES）でMAXSIZE UNLIMITEDに設定されている場合、LOBが際限なく拡張してディスクを消費する可能性があります。LOB専用表領域にはMAXSIZEの上限を必ず設定してください。

-- Step 1: 表領域の使用率を確認
SELECT tablespace_name,
       ROUND(used_space * 8192 / 1024 / 1024, 2) AS used_mb,
       ROUND(tablespace_size * 8192 / 1024 / 1024, 2) AS total_mb,
       ROUND(used_percent, 1) AS used_pct
FROM   dba_tablespace_usage_metrics
ORDER BY used_pct DESC;

-- Step 2: 該当表領域のセグメントサイズTOP10
SELECT segment_name,
       segment_type,
       owner,
       ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM   dba_segments
WHERE  tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;

-- Step 3: LOBセグメントならテーブルを特定
SELECT l.table_name,
       l.column_name,
       l.segment_name,
       ROUND(s.bytes / 1024 / 1024, 2) AS lob_mb,
       (SELECT COUNT(*) FROM all_tables t
        WHERE t.owner = l.owner
        AND t.table_name = l.table_name) AS row_count_approx
FROM   dba_lobs l
JOIN   dba_segments s
  ON   s.segment_name = l.segment_name
  AND  s.owner = l.owner
WHERE  l.segment_name = 'SYS_LOB0000078523C00004$$';

-- 1000件ずつバッチで削除
DECLARE
  v_rows  NUMBER;
  v_total NUMBER := 0;
BEGIN
  LOOP
    DELETE FROM documents
    WHERE  created_date < TO_DATE('2023-01-01', 'YYYY-MM-DD')
    AND    ROWNUM <= 1000;

    v_rows := SQL%ROWCOUNT;
    v_total := v_total + v_rows;
    COMMIT;

    DBMS_OUTPUT.PUT_LINE(
      'Deleted: ' || v_rows ||
      ' rows (Total: ' || v_total || ')'
    );

    EXIT WHEN v_rows = 0;

    -- UNDO表領域への負荷を軽減
    DBMS_LOCK.SLEEP(1);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(
    'Complete. Total deleted: ' || v_total || ' rows'
  );
END;
/

ポイント：バッチ削除完了後、LOBセグメントの領域はまだ解放されていません。削除後にALTER TABLE MOVE LOBまたはSHRINK SPACEを実行して、領域を実際に解放してください。

-- LOBセグメントのサイズ推移を記録するテーブル
CREATE TABLE lob_size_history (
  check_date       DATE DEFAULT SYSDATE,
  owner            VARCHAR2(128),
  table_name       VARCHAR2(128),
  column_name      VARCHAR2(128),
  lob_size_mb      NUMBER
);

-- 日次で実行するサイズ記録SQL
INSERT INTO lob_size_history
  (owner, table_name, column_name, lob_size_mb)
SELECT l.owner,
       l.table_name,
       l.column_name,
       ROUND(s.bytes / 1024 / 1024, 2)
FROM   dba_lobs l
JOIN   dba_segments s
  ON   s.owner = l.owner
  AND  s.segment_name = l.segment_name
WHERE  s.bytes > 100 * 1024 * 1024;  -- 100MB以上のみ記録
COMMIT;

ORA-01502: index 'HR.PK_DOCUMENTS' or partition of such index is in unusable state

-- 無効インデックスの一覧を確認
SELECT index_name, status
FROM   user_indexes
WHERE  status = 'UNUSABLE';

-- 各インデックスをREBUILD
ALTER INDEX pk_documents REBUILD;
ALTER INDEX idx_documents_date REBUILD;

ORA-10635: Invalid segment or tablespace type

-- LOBの種類を確認
SELECT table_name,
       column_name,
       securefile
FROM   user_lobs;

TABLE_NAME      COLUMN_NAME  SECUREFILE
--------------  -----------  ----------
DOCUMENTS       FILE_DATA    NO          <-- BasicFile（SHRINK不可）
LOG_ENTRIES     LOG_TEXT     YES         <-- SecureFile（SHRINK可能）

-- BasicFile LOB を SecureFile LOB に変換する
ALTER TABLE documents
MOVE LOB (file_data) STORE AS SECUREFILE (
  TABLESPACE lob_data
);

-- 変換後にインデックスを再構築
ALTER INDEX pk_documents REBUILD;