【Oracle】TEMP 表領域の肥大化を防ぐ方法｜原因特定・使用状況監視・TEMPFILE 再作成・PGA チューニングまで解説

-- TEMP 表領域の全体使用状況
SELECT tablespace_name,
       ROUND(tablespace_size/1024/1024) AS total_mb,
       ROUND(allocated_space/1024/1024) AS allocated_mb,
       ROUND(free_space/1024/1024) AS free_mb,
       ROUND(allocated_space/tablespace_size * 100, 1) AS used_pct
FROM dba_temp_free_space;

-- セッション別の TEMP 使用量（降順: 最も使っているセッションが先頭）
SELECT s.sid, s.serial#, s.username, s.program,
       s.sql_id,
       ROUND(t.blocks * 8192/1024/1024, 1) AS temp_mb,
       SUBSTR(q.sql_text, 1, 80) AS sql_text
FROM v$sort_usage t
JOIN v$session s ON t.session_addr = s.saddr
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
ORDER BY t.blocks DESC;

-- ユーザー別の TEMP 消費量合計
SELECT s.username,
       COUNT(*) AS session_count,
       ROUND(SUM(t.blocks * 8192)/1024/1024, 1) AS total_temp_mb
FROM v$sort_usage t
JOIN v$session s ON t.session_addr = s.saddr
GROUP BY s.username
ORDER BY total_temp_mb DESC;

-- V$TEMPSEG_USAGE = V$SORT_USAGE の別名（同じ内容）
SELECT username, segtype,
       ROUND(SUM(blocks * 8192)/1024/1024, 1) AS temp_mb
FROM v$tempseg_usage
GROUP BY username, segtype
ORDER BY temp_mb DESC;

-- segtype: SORT / HASH / DATA / INDEX / LOB_DATA 等
-- SORT = ORDER BY, HASH = ハッシュ結合

-- (1) 犯人を特定
SELECT s.sid, s.serial#, s.username,
       ROUND(t.blocks * 8192/1024/1024, 1) AS temp_mb,
       s.sql_id
FROM v$sort_usage t
JOIN v$session s ON t.session_addr = s.saddr
ORDER BY t.blocks DESC
FETCH FIRST 5 ROWS ONLY;

-- (2) KILL（SID と SERIAL# を指定）
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- KILL 後に TEMP が解放されるまで数秒〜数分かかる場合がある

-- 現在の TEMPFILE サイズを確認
SELECT file_name, ROUND(bytes/1024/1024) AS size_mb, autoextensible
FROM dba_temp_files;

-- RESIZE で縮小（使用中でないブロック分だけ縮小可能）
ALTER DATABASE TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' RESIZE 500M;

-- ORA-03297 が出る場合: まだ使用中のブロックがある
-- → 全セッションの TEMP 使用が解放されるまで待つか、DB 再起動

-- (1) 既存の TEMPFILE を DROP
ALTER TABLESPACE temp DROP TEMPFILE '/oracle/oradata/ORCL/temp01.dbf';

-- (2) 新しい TEMPFILE を追加（適切なサイズで）
ALTER TABLESPACE temp ADD TEMPFILE '/oracle/oradata/ORCL/temp01.dbf'
    SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

-- ※ TEMP 表領域に TEMPFILE が 1 つしかない場合は DROP できない
-- → 先に別の TEMPFILE を追加してから DROP

-- (1) 新しい TEMP 表領域を作成
CREATE TEMPORARY TABLESPACE temp_new
    TEMPFILE '/oracle/oradata/ORCL/temp_new01.dbf'
    SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

-- (2) DB のデフォルト一時表領域を変更
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;

-- (3) 全ユーザーの一時表領域を変更（任意）
-- ALTER USER app_user TEMPORARY TABLESPACE temp_new;

-- (4) 旧 TEMP 表領域を DROP（使用中のセッションがなくなってから）
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

-- PGA_AGGREGATE_TARGET の確認
SHOW PARAMETER pga_aggregate_target;

-- PGA の使用状況
SELECT name, ROUND(value/1024/1024) AS mb
FROM v$pgastat
WHERE name IN (
    'aggregate PGA target parameter',
    'aggregate PGA auto target',
    'total PGA allocated',
    'maximum PGA allocated',
    'cache hit percentage'
);

-- cache hit percentage: 100% に近いほどメモリ内で処理完了
-- 低い場合（80% 以下）は PGA の拡大を検討

-- PGA_AGGREGATE_TARGET を 2GB に拡大
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

-- 個別セッションの作業領域サイズ上限を確認
SHOW PARAMETER workarea_size_policy;  -- AUTO（デフォルト推奨）

-- グループに属する TEMP 表領域を作成
CREATE TEMPORARY TABLESPACE temp1
    TEMPFILE '/oracle/disk1/temp1.dbf' SIZE 2G
    TABLESPACE GROUP temp_group;

CREATE TEMPORARY TABLESPACE temp2
    TEMPFILE '/oracle/disk2/temp2.dbf' SIZE 2G
    TABLESPACE GROUP temp_group;

-- ユーザーにグループを割り当て
ALTER USER app_user TEMPORARY TABLESPACE temp_group;

-- グループの確認
SELECT * FROM dba_tablespace_groups;

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

-- (1) 犯人を特定
SELECT s.sid, s.serial#, s.username,
       ROUND(SUM(t.blocks)*8192/1024/1024, 1) AS temp_mb,
       s.sql_id
FROM v$sort_usage t
JOIN v$session s ON t.session_addr = s.saddr
GROUP BY s.sid, s.serial#, s.username, s.sql_id
ORDER BY temp_mb DESC;

-- (2) SQL の内容を確認
SELECT sql_fulltext FROM v$sql WHERE sql_id = 'abc123def456';

-- (3) 不要なセッションなら KILL
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- (1) 現在のサイズを確認
SELECT file_name, ROUND(bytes/1024/1024) AS size_mb FROM dba_temp_files;

-- (2) 使用中のセッションがないか確認
SELECT COUNT(*) FROM v$sort_usage;

-- (3) 0 件なら RESIZE で縮小
ALTER DATABASE TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' RESIZE 1G;

-- (1) 現在の cache hit percentage を確認
SELECT name, value FROM v$pgastat WHERE name = 'cache hit percentage';
-- 80% 以下 = ディスクソートが多い

-- (2) PGA を拡大
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

-- (3) しばらく運用して cache hit percentage が改善したか確認

-- TEMP 使用率の監視
SELECT tablespace_name,
       ROUND(allocated_space/tablespace_size * 100, 1) AS used_pct
FROM dba_temp_free_space
WHERE allocated_space/tablespace_size * 100 >= 80;
-- 結果があればアラート（メール通知等に組み込む）