René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Undo in Oracle

Oracle records all data that is about to change as undo. The information allows Oracle to undo changes in case of a rollback.

Purpose

Undo (Rollback) serves two purposes. It is the fundamental mechanism that allows the Readers don't block writers, writers don't block readers mechanism. Is makes it also possible to rollback a transaction.

Readers don't block writers, writers don't block readers

Yet to be finished...

Rolling back a transaction

Yet to be finished...

Consistent changes

If a block is taken backwards 'in time' to produce a consistent image, this is called a Consistent changes.
In contrast, a block change is the 'normal' change of data within a block (through update, insert, delete).

Misc

Transactions generate Undo (Rollback)

A Transaction generates undo. The amount of undo of a transaction can be tracked using used_urec (= Used Undo Records) in the dynamic performance view v$transaction. The following little SQL statements are meant to demonstrate this:
First, table a is created. This table is used to insert and delete rows and track the generated redo
create table a (b number, c varchar2(30));
Now, a row is inserted:
insert into a values (1,'hallo');
Let's see how many redo records this produced:
select used_urec from v$session s, v$transaction t 
 where s.audsid=sys_context('userenv', 'sessionid') and
       s.taddr = t.addr;
It generated 1 undo record. OK, so far, let's insert another record:
insert into a values (2,'foo');
And using the same statement above that selects used_urec, we find, there are 2 undo records. Now, we insert 50 record in one go:
insert into a select rownum + 50000, object_name from all_objects where rownum < 51;
A little surprisingly, we have 3 undo records now, not 52. Let's delete two records now:
delete from a where b < 4;
Again a little surprising, this generates 2 new undo records, not 1, resulting in a total of 5 undo records so far. The session is commited now.
commit;
This discards the undo records: 0 undo records. Playing the same game, but this time with a primary key:
alter table a add primary key (b);
insert into a values (3,'bar');
Selecting the number of undo records again results in 2 undo records: one for the row and one for the entry in the index for the primary key.
insert into a select rownum+70000,object_type from all_objects where rownum < 51;
This is now very surprising: it generates three new undo records, totalling in 5 undo records.
The whole things is much easier with undo tablespaces since Oracle 9i.

Automatic undo management

Automatic undo management is enabled by setting undo_management to true and by specifying an undo tablespace with undo_tablespaces.
If the undo tablespace is auto extensible, the size of the undo tablespace depends on the parameter undo_retention.

Notes

Undo is totally different from redo.