OracleのUNDO表領域でやらかしてしまった話

お仕事中にやらかしてしまったので、自戒も込めて書きます。

Oracle DatabaseにはUNDO表領域というものがあります。
UPDATE, DELETEを行った際に復元ができるように、
更新前のデータを一次退避しておくためのテーブルスペースです。

今回いいねが何をやってしまったかというと、
このUNDO表領域をいっぱいにしてしまったのです。

UNDO表領域がいっぱいになると何が起きるかというと、
テーブルの更新が走ったときに以下の３つのパターンが起きます。

UNDO表領域の自動拡張がオンになっている場合は、このパターンとなります。
この場合自動拡張される分、ディスクの容量が消費されますが、
UPDATEやDELETE文は正常に実行されます。

UNDO表領域の自動拡張がオフになっている
かつ、RETENTION GUARANTEE設定がオフになっている場合は
トランザクションの終わったUNDOデータについては逐次上書きされます。
UPDATEやDELETE文は正常に実行されます。

UNDO表領域の自動拡張がオフになっている、
かつ、RETENTION GUARANTEE設定がオンの場合は、
テーブルの更新によりUNDO表領域が不足した際にこのエラーが発生します。

今回いいねの身に起きたパターンです。

RETENTION GUARANTEE設定がオンの場合、UNDOデータのトランザクションが終了してから一定期間、UNDOデータは保存されます。
保存期間はUNDO_RETENTIONというパラメータで設定が行われます。

今回いいねは大きなテーブル更新を行っている最中に、
「あっ！UNDO表領域が絶対足りない」と気づきました。

このとき、エラーが起きるまで待つ、などをすればよかったのですが、
慌ててSQLの実行中断を行いました。
DBマシンの性能が低く、なかなか実行中断が完了しません。
まだUNDO表領域はいっぱいのままです。

なので、今度は

alter system kill session  '*****, *****'

を行いました。

「V$SESSSION.STATUSがKILLEDになったので、参照されなくなったUNDOデータは保存期間を過ぎたら解放されるだろう。」
と思ったのですが、 大間違いでした。

その後、UNDO_RETENTIONの時間を過ぎたあとにUPDATE文を実行したところ、
またしても「ORA-30036: UNDO表領域内でセグメントを拡張できません」のエラーが！！

表領域を見てみると、UNDO表領域がいっぱいのまま……。
V$SESSION.STATUSがKILLEDでもUNDOデータは解放されず、
完全にセッションが死に切るまでUNDO表領域を保持し続けてしまうようでした。