The previous table will have lots of blocks as we get about six or seven rows per block using that big data field, and my ALL_OBJECTS table has over 70,000 rows. Next, we’ll create the small table the many little transactions will modify:
SQL> create table small ( x int, y char(500) ); Table created.
SQL> insert into small select rownum, ‘x’ from all_users; 25 rows created.

Now, we’ll dirty up that big table. We have a very small undo tablespace, so we’ll want to update as many blocks of this big table as possible, all while generating the least amount of undo possible. We’ll use a fancy UPDATE statement to do that. Basically, the following subquery is finding the “first” rowid of a row on every block.

That subquery will return a rowid for every database block identifying a single row on it. We’ll update that row, setting a VARCHAR2(1) field. This will let us update all of the blocks in the table (some 8000 plus in the example), flooding the buffer cache with dirty blocks that will have to be written out. We’ll make sure we are using that small undo tablespace as well.

To accomplish this and not exceed the capacity of our undo tablespace, we’ll craft an UPDATE statement that will update just the “first row” on each block. The ROW_NUMBER() built-in analytic function is instrumental in this operation; it assigns the number 1 to the “first row” by database block in the table, which would be the single row on the block we would update:
SQL> alter system set undo_tablespace = undo_small; System altered.

OK, so now we know that we have lots of dirty blocks on disk. We definitely wrote some of them out, because we just didn’t have the room to hold them all. Next, we will open a cursor, but it won’t yet fetch a single row. Remember, when we open the cursor, the resultset is preordained, so even though Oracle did not actually process a row of data, the act of opening that resultset fixed the point in time the results must be“as of.” Now since we’ll be fetching the data we just updated and committed, and we know no one else is modifying the data, we should be able to retrieve the rows without needing any undo at all. But that’s where the delayed block cleanout rears its head.

As I said, the preceding is a rare case. It took a lot of conditions, all of which must exist simultaneously to occur. We needed blocks that were in need of a cleanout to exist, and these blocks are scarce. A DBMS_STATS call to collect statistics gets rid of them so the most common causes—large mass updates and bulk loads—should not be a concern, since the tables need to be analyzed after such operations anyway.

Most transactions tend to touch less than ten percent of the blocks in the buffer cache; hence, they do not generate blocks that need to be cleaned out. If you believe you’ve encountered this issue, in which a SELECT against a table that has no other DML applied to it is raising the ORA-­01555 error, try the following solutions:

•\ Ensure you are using “right-sized” transactions in the first place.Make sure you are not committing more frequently than you should.
•\ Use DBMS_STATS to scan the related objects, cleaning them out after the load. Since the block cleanout is the result of a very large mass UPDATE or INSERT, this needs to be done anyway.
•\ Allow the undo tablespace to grow by giving it the room to extend and increasing the undo retention. This decreases the likelihood of an undo segment transaction table slot being overwritten during the course of your long-running query. This is the same as the solution for the other cause of an ORA-01555 error (the two are very much related; you experience undo segment reuse during the processing of your query). In fact, I reran the preceding example with the undo tablespace set to autoextend 1MB at a time, with an undo retention of 900 seconds. The query against the table BIG completed successfully.
•\ Reduce the runtime of your query—tune it. This is always good if possible, so it might be the first thing you try.

One last comment, in the database that you ran the prior experiment in, don’t forget to set your undo tablespace back to the original one, for example:
SQL> alter system set undo_tablespace=undotbs2;
SQL> drop tablespace undo_small including contents and datafiles;

That way you ensure you’re not running your database with an extremely small undo tablespace.