Managing UNDO Data in Oracle

Back to: Oracle DBA Tutorials

Managing UNDO Data in Oracle

This new chapter revolves around the UNDO Operations and UNDO Data in Oracle. The UNDO tablespace is mainly used for DML operations where new data replace old data and if the user wants to return to old data.

There is a table called EMP. It consists of columns empid and sal. Empid is 100, and sal is 5000. Suppose the programmer or the user needs to update the table EMP.

Statement: UPDATE EMP SET SAL=6000 where empid=100;

The user hasn’t made any commit yet. The user who made the transaction can only see the value of 6000 because he hasn’t committed yet. The other users in the database can see the previous value of 5000. This is called read consistency.

If the user needs to roll back to the previous value of 5000, then the value 5000 is stored in another block; the block where it is stored is referred to as the undo segment, and the tablespace where the undo segments are stored is undo tablespace. Before the update statement, Oracle will save the old value into the undo segment to change the mind and roll back the data.

The Oracle database server saves the old value (undo data) when a process changes data in a database. It stores the data as it exists before modification. Retained at least until the transactions are ended.

UNDO Data used to Support:

• Rollback Operations
• Read-Consistency Queries
• Oracle Flashback Query, Oracle Flashback Transition, and Oracle Flashback Table
• Recovery from Failed Transactions

Note: A failed transaction occurs when a user session ends abnormally (possibly due to network errors or a failure on the client’s computer) before the user decides to commit or roll back the transaction. Failed Transactions may also occur when the instance crashes, or you issue the SHUTDOWN ABORT command.

How the Transaction Ends?

• Whenever a user is connected to the Oracle database and makes an insert, update, or delete statement, the transaction could contain many SQL statements. It is not necessary that the transaction contain only one SQL statement, one update statement, or one delete statement.
• The user undoes a transaction (transaction rolls back)
• The user ends a transaction (transaction commit)
• The user executes a DDL statement, such as a CREATE, DROP, RENAME, or ALTER statement.
• The session terminates abnormally because of a database crash and network down (transactions rollback).
• The user session terminates normally with an exit (transaction commits).

In the next article, I will discuss Transactions and Undo Data in Oracle. In this article, I explain how to manage UNDO Data in Oracle with examples. I hope you enjoy this article on Managing UNDO Data in Oracle.