Transactions - Oracle Database Transactions and Locking Revealed - page 95

Snapshot Too Old Error
Let's now look at the second reason developers are tempted to commit updates in a procedural loop, which arises from
their (misguided) attempts to use a “limited resource” (undo segments) sparingly. This is a configuration issue; you
need to ensure that you have enough undo space to size your transactions correctly. Committing in a loop, apart from
generally being slower, is also the most common cause of the dreaded ORA-01555 error. Let's look at this in more detail.
As you will appreciate after reading Chapter 4, Oracle's multiversioning model uses undo segment data to
reconstruct blocks as they appeared at the beginning of your statement or transaction (depending on the isolation
mode). If the necessary undo information no longer exists, you will receive an ORA-01555: snapshot too old error
message and your query will not complete. So, if you are modifying the table that you are reading (as in the previous
example), you are generating undo information required for your query. Your UPDATE generates undo information
that your query will probably be making use of to get the read-consistent view of the data it needs to update. If you
commit, you are allowing the system to reuse the undo segment space you just filled up. If it does reuse the undo,
wiping out old undo data that your query subsequently needs, you are in big trouble. Your SELECT will fail and your
UPDATE will stop partway through. You have a partly finished logical transaction and probably no good way to restart it
(more about this in a moment).
Let's see this concept in action with a small demonstration. In a small test database, I set up a table:
EODA@ORA12CR1> create table t as select * from all_objects;
Table created.
EODA@ORA12CR1> create index t_idx on t(object_name);
Index created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.
I then created a very small undo tablespace and altered the system to use it. Note that by setting AUTOEXTEND off,
I have limited the size of all UNDO to be 10MB or less in this system:
EODA@ORA12CR1> create undo tablespace undo_small
2 datafile '/u01/dbfile/ORA12CR1/undo_small.dbf'
3 size 10m reuse
4 autoextend off
5 /
Tablespace created.
EODA@ORA12CR1> alter system set undo_tablespace = undo_small;
System altered.
Now, with only the small undo tablespace in use, I ran this block of code to do the UPDATE :
EODA@ORA12CR1> begin
2 for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
3 from t
4 where object_name > ' ' )
5 loop
6 update t
7 set object_name = lower(x.object_name)
8 where rowid = x.rid;