【Oracle】TEMP表領域の肥大化原因とクリーンアップ方法

Oracleデータベースでは、ソートやハッシュ結合、大量データの一時的な処理にTEMP表領域（一時表領域）が使用されます。適切に管理されていないと、TEMP表領域が肥大化してディスクを圧迫する原因となります。

本記事では、TEMP表領域の肥大化の主な原因と、そのクリーンアップ方法を解説します。

TEMP表領域の役割と仕組み
TEMP表領域が肥大化する原因
現在のTEMP使用状況を確認するSQL
TEMP表領域をクリーンアップする方法
TEMP表領域肥大化の予防策
まとめ

TEMP表領域の役割と仕組み

TEMP表領域は、一時セグメント（temporary segment）を格納するための領域です。以下のような処理時に利用されます：

ORDER BYやGROUP BYなどのソート処理
ハッシュ結合（HASH JOIN）
索引の作成や再構築
一時表の使用時

特に、大量データの並び替えや集計でPGA（メモリ）に収まりきらない場合、TEMP表領域にディスクI/Oが発生します。

TEMP表領域が肥大化する原因

以下のような状況でTEMP表領域は膨張します：

ソートや結合対象の行数が非常に多い
インデックス未使用による全表スキャン
PGAが不足してディスクへの一時退避が頻発
一時表を多用する処理の連発
不要な大規模クエリの実行（誤った結合など）

また、TEMP表領域は自動的に空きが解放されるように設計されていますが、一部のブロックが解放されずに残るケースもあります。

現在のTEMP使用状況を確認するSQL

現在TEMP表領域がどの程度使われているかを調べるには、以下のSQLが有効です：

SELECT s.sid, s.serial#, u.tablespace, u.segfile#, u.segblk#, u.extents, u.blocks
FROM v$sort_usage u, v$session s
WHERE u.session_addr = s.saddr;

また、表領域全体の使用量は以下で確認可能です：

SELECT tablespace_name, SUM(bytes)/1024/1024 AS used_mb
FROM v$tempseg_usage
GROUP BY tablespace_name;

TEMP表領域をクリーンアップする方法

基本的には、セッション終了やSQL完了時にTEMPセグメントは解放されます。ただし、明示的にクリーンアップしたい場合や、異常なセッションが保持している場合は、以下の対処法があります。

1. セッションを特定して切断する

SELECT s.sid, s.serial#, p.spid, s.username
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid IN (
  SELECT sid FROM v$sort_usage
);

切断コマンド：

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

2. TEMPファイルを縮小（RESIZE）する

クリーンアップ後、肥大化したTEMPファイルを縮小します：

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/XE/temp01.dbf' RESIZE 512M;

※ファイルの実パスとサイズは適宜変更してください。

3. 一時表領域の再作成

TEMPファイルが縮小できない場合、TEMP表領域を再作成する方法もあります：

-- 新しいTEMP表領域を作成
CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/path/temp_new01.dbf' SIZE 512M;

-- デフォルトを変更
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

-- 古いTEMP表領域を削除
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;