Investigating Undo - Oracle Database Transactions and Locking Revealed - page 145

That UPDATE used 151 blocks to store its undo. The commit would free that up, or release it, so if we rerun the
query against V$TRANSACTION , it would once again show us no rows selected . When we update the same data—only
indexed this time—we'll observe the following:

EODA@ORA12CR1> update t set indexed = lower(indexed);
72077 rows updated.

EODA@ORA12CR1> select used_ublk
2 from v$transaction
3 where addr = (select taddr
4 from v$session
5 where sid = (select sid
6 from v$mystat
7 where rownum = 1
8 )
9 )
10 /

USED_UBLK
----------
854

As you can see, updating that indexed column in this example generated several times as much undo. This
is due to the inherit complexity of the index structure itself and the fact that we updated every single row in the
table—moving every single index key value in this structure.

ORA-01555: Snapshot Too Old Error
In Chapter 5, we briefly investigated the ORA-01555 error and looked at one cause of it: committing too frequently.
Here we'll take a much more detailed look at the causes and solutions for the ORA-01555 error. ORA-01555 is one of
those errors that confound people. It is the foundation for many myths, inaccuracies, and suppositions.

■ ORA-01555 is not related to data corruption or data loss at all. It is a “safe” error in that regard; the only
outcome is that the query that received this error is unable to continue processing.

Note

The error is actually straightforward and has only two real causes, but since there's a special case of one of them
that happens so frequently, I'll say that there are three:

• The undo segments are too small for the work you perform on your system.

COMMIT s (actually a variation on the preceding point). We covered • Your programs fetch across

this in Chapter 5.

• Block cleanout.