UNDO表領域が枯渇したらOracleエラー「Ora-30036」が出現する場合があるので対処法を考える

こんにちは。人生100年時代”わくわく”ライフプランニングコーチのアフロ（@afroriansym567）です。

ITシステム導入コンサルタントとして、人事給与勤怠関連の業務システムを取り扱う際に、OracleDBとちょくちょくお付き合いをしております。

システム運用をしている場合に、たまーーに、「Ora-30036」というOracleエラーを目にして、UNDO表領域を拡張することがありました。

拡張するときのコマンドと、本当に拡張すべきかの検討ポイントを記載します。

UNDO表領域の拡張コマンド

通常のDBFファイルのサイズを拡張する場合と同じく、ALTER DATABASE DATAFILE で良い。

ALTER DATABASE DATAFILE ‘/oracle/TESTDB/UNDOTBS01.DBF’ RESIZE 16G;
（UNDOTBS01.DBFというUNDO表領域のファイルを16GBとする）

ORA-30036 は自動 UNDO 管理にて管理している UNDO 表領域内にトランザクションで必要な UNDO
領域を獲得できなかった場合に発生するエラーです。

UNDO表領域とは何かについて、引用してきましたが、何を言っているかわからないって？

そうですね、例えば給与計算処理を実行する際に、計算処理を完了させる直前に戻る（ロールバックする）ためにデータをコピー保存しておく領域のことです。

厳密には正確ではない表現かもですが、誤ったオペレーションをしてもとに戻す必要が出た場合に、直前のデータが保存しておき元に戻すことができるようにするためのデータ保存領域のことです。

フラッシュバッククエリなどもこのUNDO表領域にデータが保存されている限りは利用ができます。

あくまでデータが保存されている限りですので、日々のシステム処理でデータが挿入、更新、削除など実施されていくと、システム処理直前のデータがどんどん上書きされていきます。

つまり、システムを利用する頻度やデータ更新容量によっては、数日前のデータでも元に戻すことも可能だけども、１日前のデータでも元に戻すことができない場合があるわけです。

Oracleエラー「Ora-30036」以外にも、「ORA-01555:スナップショットが古すぎます」が発生するケースもあります。

「スナップショットが古すぎます」というエラーの場合は、以下の２点を調査検討する必要があるかもしれません。

極論ですが、UNDO表領域は、自動拡張設定をしておけばよいかもしれません。オラクルのバージョンにもよりますが。

UNDO_RETENTIONについてだけ、数値を大きくしすぎると結局はUNDO表領域不足が発生する場合もありますので、やはりUNDO_RETENTIONとUNDO表領域の両方の適正値を検討するべきです。

V$UNDOSTATに、10分間隔で過去4日間分のUNDO関連の統計情報が格納されています。

実際のシステム運用実績のデータを元にして、算出すれば良いです。

その際の注意点としては、エラーが出る処理をその”4日間”で実施していなければ、一度は実施した上でV$UNDOSTATからデータ取得して分析をするべきです。

適正値を算出すための直接的なSQLを実行する、もしくは、V$UNDOSTATのデータをCSVファイルで出力してUNDOBLKSの最大値を確認する。

そして、以下の計算式で、UNDO表領域を算出する。

UNDOに関してはひとまず以上です。

関連記事としては、こちらも参考に。

以上。