Oracle小話その８ UNDOセグメント

とある日、NECの方から

「開発サーバの○○○○（ジョブ名）が
　ORA-30036エラーを発生させているので調べて欲しい。
　内部エラーも発生している」

という調査依頼がありました。



ORA-30036は
「UNDO表領域に使用可能なセグメントがないよーん」
という意味ですので、COMMITを細かく行なう以外、
基本的には表領域の拡張くらいしか回避方法はありません。
（ごくまれに起こるのであればほっといてもいいと思いますが）




ということで、そのジョブを調べたのですが…

特に大きなUNDOを使用するようなSQLでもない。
というか本番ではずっと動いているバッチなので
問題があるとは思えません。





そこでログを確認してみると、
SQL発行と同時にエラーが発生しているようです。


つまり、そのジョブを起動した時には
既にUNDO表領域を使う間もなかったということ。



ということは、原因は他にあるということか？




これは、UNDO情報から調べていくしかないかな…( ´-ω-)y─┛~~



しかしそこはペーパーオラクルマスターの私。
UNDOを確認する方法を忘れましたｗ

本を読み返してみたところ
V$UNDO_STATで
過去一週間のUNDO推移が確認できることが書いてあります
（こんなんあったあった）



早速確認してみると、
エラーが発生した時間帯に
NOSPACEERRCNT（全てのUNDOセグメントが使用された回数）
が発生していることが判明しました。



ほうほう、ということは
この時間帯の前後にどんなSQLが発行されたか
を調べる必要があるな…





え！そんな方法知らんがな！Σ(゜д゜lll)






しかし、知らないで終わらせるわけには行かないのでｗ
ネットで検索したところ
V$SQLのFIRST_LOAD_TIMEでなんとか検索できそうなことがわかりました。

幸いにも、エラーからまだ２日くらいしか経っていないので
SQL文も残っていました。



その中からＤＭＬ文（INSERT・UPDATE・DELETE）を探したところ、
DELETEを行なっているSQL文が１件存在！


これだ！と思って調べてみると
「DELETE /*+NESTED_TABLE_SET_REFS+*/ FROM ～」
というSQL文が。
ん？なんだこのヒント文？

そのレコードのmodule列を見てみるとsqlldrの文字が…。



ということは、SQLローダのDELETE文が悪さしているに違いない！
（REPLACEキーワードであればDELETE→INSERTだし）

早速そのローダのコントロールファイルを調べてみると
確かに「REPLACE」キーワードを使用しています。



うーん、しかし一体誰がその時間帯にロードしたのか…
一応、隣にいる取締役に聞いてみたところ


取締「あー、そういえばロードしてたお」

（やっぱり）あんたかー！(゜Д゜；)



取締「でも、いつもはロード前にちゃんとTRUNCATEしてるお」

俺「えー、そうなんですか」

ということはUNDOセグメントは使わないはず…

では何故だろう（・ω・）？

と、あれこれと考え込んでいたところに…





取締「あ！そういえば」

俺「な、なにか？」

取締「その時間、ロードキャンセルしようとしてunixコマンドでkillしたら
　　　オブジェクトがロックされちゃったから
　　　そのまま帰っちゃったお」

（やっぱり）あんたかー！(゜Д゜；)

取締「すまんですなー」




というわけで、killコマンドがなんらかのおかしな事を引き起こして
無限ループしちゃったのかな？
それが原因でUNDOを満杯にしちゃったと。
なるほど。

予測に過ぎませんが、納得いく原因が判明して一件落着。


でもローダ関係の内部エラーって結構多いなー。
中で何か変なことしてるのかな？



＜まとめメモ＞
■データベース内の全てのUNDOセグメント名と状態の情報（オフライン込み）
　DBA_ROLLBACK_SEGS

■オンライン状態のUNDOセグメントの情報
　V$ROOLNAME、V$ROLLSTAT

■現在アクティブなトランザクションによるUNDOセグメント使用状態に関する情報
　V$TRANSACTIONS、V$SESSION

■インスタンス実行中に生成されたUNDOブロックの量を取得（過去１週間分保持）
　V$UNDOSTAT

■ある時間帯にどのSQL文が発行されたかを知る方法
　・V$SQL文の「FIRST_LOAD_TIME」「LAST_LOAD_TIME」で検索
　（ただし、直近のSQL文しか保存されていないので注意）
　・LOGMINERを使用
　・監査を使用