Oracleエラー対応（ORA-01652）：一時表領域枯渇時の調査方法及び解決策について

エラーメッセージ

ORA-01652：一時セグメントをstring(表領域string)で拡張できません

原因

該当する一時表領域にて、空き領域が枯渇しエクステントが割り当てられない場合にORA-01652 が発生します。

一時表領域とは

主にソート処理で使用されます。通常ソート処理はメモリ上で行われますが、メモリ上のみでは領域が不足する場合に一時表領域が使用されます。

一時表領域はデータベース全体で共通領域として使用されることが多く、もし ORA-01652 が発生し空き領域が枯渇している場合、その他の重要な処理にて一時表領域が使用できず、連鎖的に処理が異常終了してしまう可能性があります。

調査方法

ORA-01652 が発生した場合、どのような処理が原因となったのか調査する必要があります。
そのため、今回はいくつかの調査方法についてご紹介します。

EMの利用

Oracle Enterprise Manager を利用して、ORA-01652 が発生した時刻の処理状況を確認します。
SELECT処理など負荷が高いSQLが実行されているかどうかがポイントです。

◆EM操作例

下記の順番でEM画面をクリックしていき、ASH分析画面やトップアクティビティ画面にて表示される処理の一覧から負荷が高そうなSELECT処理のSQLを確認(※)

セッション情報の確認

DBA_HIST_ACTIVE_SESS_HISTORY や V$ACTIVE_SESSION_HISTORY、V$SESSION から ORA-01652 が発生した時刻のセッション情報を確認します。(※)
なお、V$SESSION は現在のセッション情報を確認できます。

◆確認SQL例

SQL> SELECT sample_time, session_id, session_type, user_id, sql_id, sql_opname, event, program, module, client_id, machine, temp_space_allocated		
  2    FROM dba_hist_active_sess_history		
  3   WHERE sample_time BETWEEN TO_DATE('2020XXXX 16:00:00','YYYYMMDD HH24:MI:SS') 		
  4     AND TO_DATE('2020XXXX 16:15:00','YYYYMMDD HH24:MI:SS')		
  5   ORDER BY sample_time		
  6  /		

• SAMPLE_TIME 列には ORA-01652 の発生時間帯を指定します
• EVENT 列結果に「direct path write temp」のような TEMP 関連の待機イベントが発生している場合、ORA-01652 の発生起因となる SQL の可能性が高いです
• USER_ID 列では SQL の実行ユーザーを確認できます
• ＜PROGRAM 列・MODULE 列・CLIENT_ID 列・MACHINE 列＞では、どのようなサーバやアプリ、端末からSQLが実行されているのか確認できます
• TEMP_SPACE_ALLOCATED 列ではTEMPメモリーの使用量（バイト）を確認できるため、まずはこの数値が大きいセッションを確認することも有効的です

※必要ライセンスについて
EMのASH分析/トップアクティビティ画面や V$ACTIVE_SESSION_HISTORY、DBA_HIST_ACTIVE_SESS_HISTORY を使用するには、Oracle Database Enterprise Edition かつオプションのライセンスが必要となります。

解決策

さて、ORA-01652 の発生原因が判明すれば、どのような対処をすれば良いかが見えてきます。
解決策についてもいくつかご紹介していきます。

一時表領域の拡張

領域不足で発生するエラーのため、サイズ拡張することで解消されます。ORA-01652 が頻発してしまう場合などは表領域拡張をご検討ください。

特定ユーザー専用の一時表領域を作成

特定のSQL処理（またはアプリ処理）で毎回 ORA-01652 が発生していることが判明した場合、処理を実行しているユーザー専用の一時表領域を作成することも有効的です。

特定ユーザー専用の一時表領域を使用することで、その他の重要な処理が連鎖的に失敗する、という事象を防ぐことができます。

◆SQL例

1）一時表領域作成

SQL> CREATE TEMPORARY TABLESPACE <一時表領域名> TEMPFILE 'XXXXX.dbf' SIZE 5000M;

2）既存ユーザーの一時表領域割当変更

SQL> ALTER USER <ユーザー名> TEMPORARY TABLESPACE <一時表領域名>;

SQLのチューニング

特定のSQL処理でのみ一時表領域が多く使用されていることが判明した場合、その問題SQLのソート処理負荷を軽減するなど、SQLチューニングを検討することも有効的です。

SELECT全件検索に注意

実際に担当している業務で発生したことがある事象なのですが、データ件数が膨大なテーブルをWHERE句などの条件を細かく指定せずにSELECT処理を実行した場合に、ソート処理の領域が足りず ORA-01652 となってしまうことがあります。
そのため、SELECT文の全件検索時は注意が必要です。

最後に

まずはどのような処理が原因で ORA-01652 が発生したのかを把握し、状況によって適切な解決策を検討することをお勧めいたします。