ORA-30036 string(UNDO表領域'string'内)でセグメントを拡張できません

エラーメッセージ

ORA-30036: string(UNDO表領域'string'内)でセグメントを拡張できません
原因: 指定したUNDO表領域には、使用可能な領域がこれ以上ありません。
処置: UNDO表領域に領域を追加して、操作を再試行してください。または、アクティブ・トランザクションがコミットされるまで待機してください。

ORA-29250〜ORA-32775

対処

• アクティブ・トランザクションを commit or rollback する。
• UNDO表領域を拡張する。
  • 既存データファイルを拡張する。
  • データファイルを追加する。
• UNDO表領域を自動拡張するようにする。

関連

• 表領域を拡張する - ablog
• UNDO表領域の容量見積 - ablog
• 自動UNDOチューニングについて - ablog
• UNDO表領域使用率の警告アラート閾値を変更してみる - ablog
• Oracle はどのようにして読み取り一貫性をとっているか - ablog
• 肥大した UNDO 表領域を縮小する - ablog

参考

• KROWN:36335
• UNDO表領域の管理
• [Oracle] UNDO表領域の必要容量見積もり方法 | Archive Redo Blog

追記:
UNDO表領域の見積。

UNDOの保存に必要な領域 = (UR * UPS + OVERHEAD) * DBS

個々の項目内容は次の通りである。
１．(UR) UNDO_RETENTION(秒)
２．(UPS) 1秒間に使用する(作られる)UNDOブロックの数
３．(OVERHEAD) メタデータ使用時(トランザクション表等)のオーバーヘッド
４．(DBS) DB_BLOCK_SIZE

２番目の値を求めるには、動的パフォーマンス・ビュー（V$UNDOSTAT）を使用
する必要がある。このビューは、UNDO領域の監視とチューニングを行うための
統計情報が含まれている。ビュー内の各行には、インスタンス内で10分ごとに
収集された統計が表示され、現行の作業負荷に必要なUNDO領域の量を見積もる
ことが可能である。また、このビューには7日サイクルによる合計1008行が含
まれる。

以下を実行すると、１秒間に使用するUNDOブロックの数が計算される。

SQL> SELECT (SUM(UNDOBLKS))/SUM((END_TIME-BEGIN_TIME)*86400)
     FROM V$UNDOSTAT;

BEGIN_TIMEとEND_TIMEがDATEデータのために、秒に変更するため
86400(60*60*24)を掛けている。

では、1秒間に使用する(作られる)UNDOブロックの数を大体100ぐらいにし、
overheadは24とする。また、保存期間はフラッシュバック等を行ないたい為
3時間（10800）にする。

この条件でUNDO表領域を求めると
UNDOの保存に必要な領域 = (UR * UPS + OVERHEAD) * DBS

SQL> SELECT (((UR * UPS + 24) * DBS)/1024)/1024 AS "UNDO SIZE(MB)"
     FROM
    (SELECT VALUE AS UR
     FROM V$PARAMETER
     WHERE NAME = 'UNDO_RETENTION'),
    (SELECT (SUM(UNDOBLKS)/SUM((END_TIME - BEGIN_TIME) * 86400))
     AS UPS
     FROM V$UNDOSTAT),
    (SELECT VALUE AS DBS
     FROM V$PARAMETER
     WHERE NAME = 'DB_BLOCK_SIZE');

UNDO情報の保存に必要な領域は、約16GBになる。思っていた以上に大きなサイ
ズが必要という結果となった。

UNDOに関する検証 その５ | Insight Technology, Inc.

今回は、UNDO表領域本来のロールバックという観点からではなく、フラッシュ
バックを行なうためのデータを保存する期間という点に主観を置いて検証して
いく。

例えば、UNDO表領域で使用するファイルシステムは4GBまでの制限があると仮
定する。以下が今回の設定値の内容である。

- ブロックサイズは2K (DBS)
- 負荷のピーク時の1秒間に使用する(作られる)UNDOブロックの数は
大体40ぐらい (UPS)
- OVERHEADを24

以下の式を基にUNDO_RETENTIONを算出する。

UNDO_RETENTION = (UNDOデータの保存に必要な領域 / DBS - OVERHEAD) / UPS

実際にSQL文を発行する。

SQL> SELECT (((4 * 1024 * 1024 * 1024) / DBS - 24) / UPS)
     as "UNDO_RETENTION"
     FROM
    (SELECT (SUM(UNDOBLKS)/SUM((END_TIME - BEGIN_TIME) * 86400))
     AS UPS
     FROM V$UNDOSTAT),
    (SELECT VALUE AS DBS
     FROM V$PARAMETER
     WHERE NAME = 'DB_BLOCK_SIZE');

UNDO_RETENTION
52428.2

UNDOデータを約14時間保存できることを意味する。すなわち、今回のケースで
は理論上、過去14時間以内の情報であれば、フラッシュバック機能を利用し、
過去のある時点に戻って検索を行なったりすることができる。実際はその環境
毎に必要なオーバヘッドや、理論値に達した時に、更にロールバックができる
十分な領域を考慮するとUNDOデータの保存期間は短くなる。

UNDOに関する検証 その６ | Insight Technology, Inc.

う〜ん、さすがインサイト・テクノロジー。

追記(2010/03/26):
UNDO 表領域にデータファイル追加してたいおうする場合の手順。

SQL> ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/u02/oradata/orcl/undotbs02.dbf' SIZE 4096M REUSE AUTOEXTEND OFF;