Undo Retention Period in Oracle

Back to: Oracle DBA Tutorials

Undo Retention Period in Oracle

In this article, we will try to learn about the most important topic, the Undo Retention Period in Oeacle. Please read our previous article discussing Automatic Undo Management in Oracle with examples.

Undo Retention Period in Oracle

When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time the Oracle database attempts to retain old undo information before overwriting it. The retention period refers to the minimum amount of time the Oracle database attempts to retain all the undo information before overwriting it.

After a transaction is committed, undo data is no longer needed for rollback or the transaction recovery process. However, long-running queries may require this old undo information for consistent reading purposes to produce older data block images. Furthermore, the success of several Oracle flashback features can also depend upon the availability of older undo information.

The Oracle flashback query simply retrieves data at a specific point. For example, I need to know whether the salary of the employee is 100 before five minutes or ten minutes. Oracle needs the undo data or old data for long-running queries and also for the Oracle flashback query. For these reasons, retaining the old undo information for as long as possible is desirable.

There is a UNDO_RETENTION parameter whose default value is 900s, which is 15 minutes. So, the oracle will keep the old undo information for 15 minutes if I have a long-running or flashback query. This indicates that retention for 15 minutes. We will not get the information if we look for the salary values before 25 minutes because the retention period is set to 15 minutes. To understand more about undo retention, let’s take an example.

We have user A. He ran a select query in the database at 10:00 AM. The query will take 5 minutes to complete. User A should read the data present at 10:00 AM exactly. This is referred to as read consistency. Now, the query takes 5 minutes. Within that 5 minutes, if another developer logs into the database and makes changes, user A will still see that data present at 10:00 AM.

User B logs in to the database and makes the salary 0. Suppose the update statement will take less than 1s. When he updates the sal to 0, the user A query will still be running, and the user A will not see that salary as). This is because the query is executed at 10:00 am. Even if the query takes 1 hour, User A will still see the old data.

So, Undo retention is very important. There are many constraints, but we just need to focus on two main things: read consistency and retrieving old undo information. Oracle needs to retrieve the undo information only for two cases: the long-running query and the Oracle flashback query.

We have to keep the retention value for the undo tablespace higher than that for the longest query. For example, if the query takes 10 minutes to execute, then the undo retention should be 15 minutes. If the query is taking more than 15 minutes, then there is a serious problem with it. The query running in the database should take less than 10 minutes to execute.

In the next article, I will discuss Categories of UNDO in Oracle. In this article, I explain the Undo Retention Period in Oracle with examples. I hope you enjoy this Undo Retention Period in the Oracle article.