9-9. Monitoring and Administering Undo

In order to minimize the impact to the users, proactively monitor the alert log for the below errors and resize the undo tablespace accordingly:

  • ORA-01650 unable to extend rollback segment error message: happens when the undo tablespace runs out of room for undo data. Make sure users commit large transactions periodically.
  • ORA-01555 snapshot too old error message: occurs when the query needs to access undo information that has already been overwritten. This may happen in a long-running or flashback query. Configure an appropriate undo retention interval by setting UNDO_RETENTION in seconds and altering tablespace to have RETENTION GUARANTEE. This parameter UNDO_RETENTION is only used when:
    - the undo tablespace has the AUTOEXTEND option enabled or
    - you want to set undo retention for LOBS or
    - you want to guarantee retention.

The recommendation is to set the AUTOEXTEND to true until we determined the space requirements for the undo tablespace. After that, we should disable it.

April 2, 2008. undo.

No Comments Yet

Be the first to comment!

Leave a Reply

Trackback URI