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

Now, while that PL/SQL block of code is running, we will run a query in another session. That other query will
read table T and process each record. It will spend about 1/100 of a second processing each record before fetching the
next (simulated using DBMS_LOCK.SLEEP(0.01) ). We will use the FIRST_ROWS hint in the query to have it use the index
we created to read the rows out of the table via the index sorted by OBJECT_ID . Since the data was randomly inserted
into the table, we would tend to query blocks in the table rather randomly. This block will only run for a couple of
seconds before failing:

EODA@ORA12CR1> declare
2 cursor c is
3 select /*+ first_rows */ object_name
4 from t
5 order by object_id;
6
7 l_object_name t.object_name%type;
8 l_rowcnt number := 0;
9 begin
10 open c;
11 loop
12 fetch c into l_object_name;
13 exit when c%notfound;
14 dbms_lock.sleep( 0.01 );
15 l_rowcnt := l_rowcnt+1;
16 end loop;
17 close c;
18 exception
19 when others then
20 dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
21 raise;
22 end;
23 /
rows fetched = 159
declare
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 16 with name
"_SYSSMU16_587457654$" too small
ORA-06512: at line 21

As you can see, it got to process only 159 records before failing with the ORA-01555: snapshot too old error.
To correct this, we want to make sure two things are done:

UNDO_RETENTION is set in the database to be at least long enough for this read process to
complete. That will allow the database to grow the undo tablespace to hold sufficient undo for
us to complete.

•

• The undo tablespace is allowed to grow or you manually allocate more disk space to it.