Home > Oracle in general > ORA-01555 (snapshot too old) error although undo_retention set to a high value

ORA-01555 (snapshot too old) error although undo_retention set to a high value

The past week i had a argumentation with a colleague of mine on the behavior of the parameter undo_retention in 10g.

He stated in 10g undo_retention determines the lower number of seconds oracle keeps the undo records. If there is enough space available an ORA-01555 should not be raised.

I knew there are cases in which undo_retention is set to a ridiculous high value (for instance a week), the undo tablespace has a lot of free space but an ORA-01555 is raised nonetheless. So we took a look at the documentation:

For Oracle 10g Release 1 the documentation states:

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. The system retains undo for at least the time specified in this parameter and automatically tunes the undo retention period to satisfy the undo requirements of the queries.

But for Oracle 10g Release 2 the documentation says different:

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

In Oracle 11g Release 1 and Oracle 11g Release 2 the behavior is the same as for Oracle 10g Release 2.

Categories: Oracle in general Tags:
  1. No comments yet.
  1. No trackbacks yet.