Database Reference
In-Depth Information
9 if ( mod(x.r,100) = 0 ) then
10 commit;
11 end if;
12 end loop;
13 commit;
14 end;
15 /
begin
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-06512: at line 2
I get the error. I should point out that I added an index hint to the query and a WHERE clause to make sure I was
reading the table randomly (together, they caused the cost-based optimizer to read the table “sorted” by the index key).
When we process a table via an index, we tend to read a block for a single row, and then the next row we want will be
on a different block. Ultimately, we will process all of the rows on block 1, just not all at the same time. Block 1 might
hold, say, the data for all rows with OBJECT_NAME s starting with the letters A, M, N, Q, and Z. So we would hit the block
many times, since we are reading the data sorted by OBJECT_NAME and presumably many OBJECT_NAME s start with
letters between A and M. Since we are committing frequently and reusing undo space, we eventually revisit a block
where we can simply no longer roll back to the point our query began, and at that point we get the error.
This was a very artificial example just to show how it happens in a reliable manner. My UPDATE statement was
generating undo. I had a very small undo tablespace to play with (10MB). I wrapped around in my undo segments
many times, since they are used in a circular fashion. Every time I committed, I allowed Oracle to overwrite the undo
data I generated. Eventually, I needed some piece of data I had generated, but it no longer existed and I received the
ORA-01555 error.
You would be right to point out that in this case, if I had not committed on line 10, I would have received the
following error:
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_SMALL'
ORA-06512: at line 6
The major differences between the two errors are as follows:
The
ORA-01555 example left my update in a totally unknown state . Some of the work had been
done; some had not.
nothing I can do to avoid the ORA-01555 error , given that I committed in
the cursor FOR loop.
The ORA-30036 error can be avoided by allocating appropriate resources in the system. This
error is avoidable by correct sizing; the first error is not. Further, even if I don't avoid this error,
at least the update is rolled back and the database is left in a known, consistent state—not
halfway through some large update.
There is absolutely
The bottom line here is that you can't “save” on undo space by committing frequently—you need that undo.
I was in a single-user system when I received the ORA-01555 error . It takes only one session to cause that error, and
many times even in real life it is a single session causing its own ORA-01555 errors. Developers and DBAs need to work
together to size these segments adequately for the jobs that need to be done. There can be no short-changing here.
You must discover, through analysis of your system, what your biggest transactions are and size appropriately for them.
 
Search WWH ::




Custom Search