About Managing Undo Data

Although by default Oracle Database manages undo data and the undo tablespace automatically, if your installation uses Oracle Flashback features, then you may have to perform some undo management tasks to ensure the success of these operations.

Oracle Flashback operations resulting in snapshot too old errors indicate that you must intervene to ensure that sufficient undo data is retained to support these operations.

The following methods better support Oracle Flashback operations:

• Set the minimum undo retention period for the autoextending tablespace to be as long as the longest expected Oracle Flashback operation.

  You achieve this goal by setting the UNDO_RETENTION initialization parameter. See Oracle Database Administrator's Guide for details.

• Change the undo tablespace to a fixed size.

  For an autoextending undo tablespace, Oracle Database always automatically tunes the undo retention period to be slightly longer than the longest-running active query. However, this autotuned retention period may be insufficient to accommodate Oracle Flashback operations. If the undo tablespace has autoextending disabled, or has a fixed size, then Oracle Database uses a different method for tuning the undo retention period to better accommodate Oracle Flashback operations.

  To change the undo tablespace to a fixed size, you must choose a tablespace size that is sufficiently large. If you choose an undo tablespace size that is too small, then the following errors could occur:

  • DML could fail because there is not enough space to accommodate undo data for new transactions.

  • Long-running queries could fail with a snapshot too old error, which means that there was insufficient undo data for read consistency.

  Oracle Enterprise Manager Database Control (Database Control) includes an Undo Advisor to help you determine the minimum size for the fixed size of the undo tablespace. See "Computing the Minimum Undo Tablespace Size Using the Undo Advisor".