Understanding Oracle Undo Records: A Back-to-Basics Guide

17 Mar Understanding Oracle Undo Records: A Back-to-Basics Guide

I had a chat a couple of weeks ago with my mentor on how we are the last of our species that know and drilled down on understanding how the Oracle Database works, so here I am doing a series of blog posts going back to basics aiming to demystify the database to better understand it.
This entry is a bit lengthy, as I’ll try to provide a brief explanation of what undo records are and information that pertain to it.

What is a Transaction?

A transaction is a logical and atomic unit of work that contains one or more SQL statements. A transaction groups SQL operations so that either all operations are committed, meaning they are applied to the database, or all are rolled back, meaning they are undone from the database. A transaction must have ACID properties.

What is an Undo Record?

Basically, it’s information used by Oracle to roll back changes we’ve made to our data in a transaction. It primarily has the following three uses:

• Undoing data in our transaction when we execute the ROLLBACK command: When we perform a rollback, undo records are used to reverse changes in the database from our uncommitted transaction.

• Ensuring read consistency of data: Oracle uses undo records to maintain a consistent image of the data while it’s being modified by another user.

• Recovering the Database: Undo records are used in database recovery to undo any uncommitted changes in our data that are present in the datafiles.

This record is managed in a special undo tablespace. You can see which tablespace is of type undo in the cdb_tablespaces view:

TESTDB> select tablespace_name
from cdb_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS

In this tablespace, only undo segments are stored, each composed of at least two undo extents. Undo segments are written in a circular manner.

In this type of tablespace, we cannot store a table or an index; the RDBMS does not allow it. It’s also important to know that the database allows us to have more than one undo tablespace, but only one can be active at a time.

There are two types of undo record management: Manual or Automatic, with the default in 11g forward being Automatic. In Automatic mode, undo records are managed in what are known as undo segments. In Manual mode, undo records are managed in rollback segments, and the undo tablespace is not used.

Undo information is divided into three categories:

• Uncommitted or Active Undo Information: Supports an ongoing transaction and is required if a user wants to perform a rollback or if the transaction fails. This type of information is never overwritten.

• Committed or Unexpired Undo Information: This information is no longer required by the transaction but is necessary to meet the time interval defined in UNDO_RETENTION.

• Expired Undo Information: This information is no longer required by a transaction and can be overwritten by the next active transaction.

As mentioned above, undo records also help maintain read consistency. What happens when a query wants to obtain information from the undo record, but this information has already been overwritten? If this happens, you’ll see the following error: ORA-01555 Snapshot Too Old. This error indicates that the query needs a “snapshot” of the data as it was at a past point in time, and reconstructing this “snapshot” requires undo record information that is no longer available.

Oracle uses an undo retention managed in seconds. When the undo tablespace is managed with the AUTOEXTEND option, Oracle retains undo information for at least the time specified by the UNDO_RETENTION parameter. However, it’s important to note that if the tablespace doesn’t have enough space to grow, it will start using space occupied by unexpired information, which can cause an ORA-01555 error.

Now that we know what they are, what can I do with this information?
Let’s look at a small example using the hr user, where you’ll be able to see a transaction and the space it is consuming from undo.

First of all, let’s check the SID assigned to us and create a table called prueba.

TESTDB >select sys_context('USERENV','SID') from dual;
SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------------
69

TESTDB >create table prueba (idprueba number);
Table created.

With the following query, we verify that no transaction is active in our session. The result should be a null value.

TESTDB >select dbms_transaction.local_transaction_id from dual;
LOCAL_TRANSACTION_ID
--------------------------------------

Then, we create a small PL/SQL block that inserts data into the table we created, and it is very important that you do not commit it.

TESTDB >begin
  2  for i in 1..10000 loop
  3  insert into prueba values (i);
  4  end loop;
  5  end;
  6  /
PL/SQL procedure successfully completed.

We verify the transaction again, and you will notice that since we performed an INSERT and have not committed it, we get a result. This means that our transaction has been assigned an identifier or XID.

TESTDB >select dbms_transaction.local_transaction_id from dual;
LOCAL_TRANSACTION_ID
--------------------------------------
48.19.18324

These three numbers appear in the following order:

• The identifier of the undo segment where the transaction records its undo information.
• The undo slot number in the undo segment.
• The sequential number in the undo slot.

However, the information shown above is useful to know because we often need to monitor transactions that do not belong to us. That is why there is a view called v$transaction, where we can check transactions happening elsewhere. In this view, you will find three fields that will look very familiar:

• XIDUSN – The identifier of the undo segment where the transaction records its undo information.
• XIDSLOT – The undo slot number in the undo segment.
• XIDSQN – The sequential number in the undo slot.

With the query below, you can see the relationship with the session and the amount of undo being used by the session in megabytes:

select r.name,s.sid,s.serial#,s.module,
    to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,
    xidusn, xidslot, xidsqn,
    t.used_ublk * to_number(x.value)/(1024*1024) as undo_mb
from v$session s,v$transaction t,v$rollname r,v$parameter x
where s.saddr = t.ses_addr and r.usn = t.xidusn
    and x.name = 'db_block_size';


Rollback Segment      SID,Serial   Module               Login Time            XIDUSN    XIDSLOT     XIDSQN      Undo MB
--------------------- ------------ -------------------- ----------------- ---------- ---------- ---------- ------------
_SYSSMU47_1546937756$ 69,1043      SQL*Plus             03/17/25 18:35:40         48         19      18324        .9041

Conclusion
We learned what a transaction is, as well as the definition of undo. Likewise, once a transaction starts, the information before being committed (UNCOMMITTED) is stored in an undo record, allowing for a rollback and ensuring data read consistency once it has been committed (COMMITTED).