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

To demonstrate this effect, we can set up a small, but somewhat artificial test. We'll create a very small undo
tablespace with one session that will generate many small transactions, virtually assuring us that it will wrap around
and reuse its allocated space many times—regardless of the UNDO_RETENTION setting, since we are not permitting
the undo tablespace to grow. The session that uses this undo segment will be modifying a table, T . It will use a full
scan of T and read it from “top” to “bottom.” In another session, we will execute a query that will read the table T via
an index. In this fashion, it will read the table somewhat randomly: it will read row 1, then row 1,000, then row 500,
then row 20,001, and so on. In this way, we will tend to visit blocks very randomly and perhaps many times during
the processing of our query. The odds of getting an ORA-01555 error in this case are virtually 100 percent. So, in one
session we start with the following:
EODA@ORA12CR1> create undo tablespace undo_small
2 datafile '/tmp/undo.dbf' size 2m
3 autoextend off
4 /
Tablespace created.
EODA@ORA12CR1> alter system set undo_tablespace = undo_small;
System altered.
Now, we'll set up the table T to query and modify. Note that we are ordering the data randomly in this table. The
CREATE TABLE AS SELECT tends to put the rows in the blocks in the order it fetches them from the query. We'll just
scramble the rows up so they are not artificially sorted in any order, randomizing their distribution:
EODA@ORA12CR1> drop table t purge;
Table dropped.
EODA@ORA12CR1> create table t
2 as
3 select *
4 from all_objects
5 order by dbms_random.random;
Table created.
EODA@ORA12CR1> alter table t add constraint t_pk primary key(object_id);
Table altered.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=> true );
PL/SQL procedure successfully completed.
And now we are ready to do our modifications:
EODA@ORA12CR1> begin
2 for x in ( select rowid rid from t )
3 loop
4 update t set object_name = lower(object_name) where rowid = x.rid;
5 commit;
6 end loop;
7 end;
8 /