Database Reference
In-Depth Information
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.
For this example, I have determined my long-running process takes about 720 seconds to complete (I have about
72,000 records in the table, so at 0.01 seconds per row we have 720 seconds). My UNDO_RETENTION is set to 900 (this is
in seconds, so the undo retention is about 15 minutes). I altered the undo tablespace's data file to permit it to grow by
1MB at a time, up to 2GB in size:
EODA@ORA12CR1> alter database
2 datafile '/tmp/undo.dbf'
3 autoextend on
4 next 1m
5 maxsize 2048m;
Database altered.
When I ran the processes concurrently again, both ran to completion. The undo tablespace's data file grew this
time, because it was allowed to and the undo retention I set up said to.
EODA@ORA12CR1> select bytes/1024/1024
2 from dba_data_files
3 where tablespace_name = 'UNDO_SMALL';
BYTES/1024/1024
---------------
21
Search WWH ::




Custom Search