Transactions and Undo Data in Oracle

Back to: Oracle DBA Tutorials

Transactions and Undo Data in Oracle

In this article, we will learn more about the Transactions and Undo Data in Oracle. In the previous article, we discussed the Undo Data Overview in Oracle. We will now see that the undo data is stored in the undo segment.

Transactions and Undo Data in Oracle

• Each transaction is assigned to only one undo segment
• An undo segment can service more than one transaction at a time.
• When a transaction starts, it is assigned to an undo segment. Throughout the life of the transactions, when data is changed, the original values are copied into the undo segment. You can see which transactions are assigned to which undo segments by checking the V$TRANSACTION dynamic performance view.
• Undo Segments are specialized segments automatically created by the database server as needed to support transactions.
• Like all segments, undo segments are made up of extents, which in turn consist of data blocks. Undo segments automatically grow and shrink as needed, acting as a circular storage buffer for their assigned transactions. We get these kinds of issues during the data migration. Sometimes, we issue 10,000 records, and then we issue a commit, and issuing a commit multiple times will clear the undo tablespace.
• Undo information is stored in undo segments, which are stored in an undo tablespace.

Note: You cannot create other segment types, such as tables, in the undo tablespace. Oracle will create multiple tablespaces while creating a database, but if we create a table in the undo tablespace, Oracle gives us an error.

The Database Configuration Assistant (DBCA) automatically creates a smallfileundo tablespace. You can also create a bigfileundo tablespace. However, contention could occur on the file header in a high-volume online transaction processing (OLTP) environment with many short concurrent transactions. An undo tablespace, stored in multiple data files, can resolve this potential issue. Although a database may have many undo tablespaces, only one of them at a time can be designated as the current undo tablespace for any instance in the database.

Undo segments are automatically created and always owned by SYS. Because the undo segments act as a circular buffer, each segment has a minimum of two extents. The default maximum number of extents depends on the database block size but is very high (32,765 for an 8 KB block size). Undo tablespaces are permanent, locally managed tablespaces with automatic extent allocation. The database automatically manages them. Because undo data is required to recover from failed transactions (such as those that may occur when an instance crashes), undo tablespaces can be recovered only while the instance is in the MOUNT state.

Let’s do an exercise.

While performing this exercise, we must ensure that the user has DBA privileges because we need to view the data dictionary v$session and v$transaction.

Connect to the pluggable database.

Check the user and container name.

Command: show con_name

Step 1: Create a table called emp.

We are creating a table with columns n and salary.

So, the table is now created. Let’s now try to insert two records into the table.

insert into emp values (1,500);
insert into emp values (2,400);

We have issued the insert statements without the commit statement. Currently, the user pdbts_admin can only see these values, and no other user cannot see these records.

Step 2: Check the Transaction.

We have a query that uses the v$session and v$transaction to display the session details.

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;

We can see the sid is 25, the serial number is 14071, and the username is PDBTS_ADMIN. There are two other columns, USED_UREC and USER_UBLK.

• USED_UREC = No. Of records used.
• USED_UBLK = No. Of blocks used.

Here, we have USED_UREC 2 because we have inserted two records. We haven’t made any commit yet. Let’s try inserting another value into the table again.

Statement: insert into emp values (3,700 );

Another record is inserted into the table. Now, let us recheck the session details.

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;

As we have inserted another record, we can see that the parameter USED_UREC has also been changed to 3. Now, let’s try to commit.

Now, let us recheck the session details.

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;

We can see that after issuing the commit, no records were found.

Note: If we insert millions of records into the table, they cannot stay in the undo tablespace for a long time because of the undo retention.

In the next article, I will discuss Comparing Undo Data and Redo Data in Oracle. In this article, I explain transactions and undo data in Oracle with examples. I hope you enjoy this Transactions and Undo Data in Oracle article.