This cause of the ORA-01555 error is hard to eliminate entirely, but it is rare anyway, as the circumstances under which it occurs do not happen frequently. We have already discussed the block cleanout mechanism, but to summarize, it is the process whereby the next session to access a block after it has been modified may have to check to see if the transaction that last modified the block is still active.

Once the process determines that the transaction is not active, it cleans out the block so that the next session to access it does not have to go through the same process again. To clean out the block, Oracle determines the undo segment used for the previous transaction (from the block’s header) and then determines whether the undo header indicates that the transaction has been committed and, if so, when it committed.

This confirmation is accomplished in one of two ways. One way is that Oracle can determine that the transaction committed a long time ago, even though its transaction slot has been overwritten in the undo segment transaction table. The other way is that the COMMIT SCN is still in the transaction table of the undo segment, meaning the transaction committed a short time ago, and its transaction slot hasn’t been overwritten.

To receive the ORA-01555 error from a delayed block cleanout, all of the following conditions must be met:

•\ A modification is made and COMMITed, and the blocks are not cleaned out automatically (e.g., the transaction modified more blocks than can fit in ten percent of the SGA block buffer cache).

•\ These blocks are not touched by another session and will not be touched until our unfortunate query (displayed shortly) hits it.

•\ A long-running query begins. This query will ultimately read some of those blocks from earlier. This query starts at SCN t1, the read-­consistent SCN it must roll data back to in order to achieve read consistency. The transaction entry for the modification transaction is still in the undo segment transaction table when we begin.

•\ During the query, many commits are made in the system. These transactions don’t touch the blocks in question (if they did, we wouldn’t have the impending problem as they would clean out the old transaction—solving the cleanout issue).

•\ The transaction tables in the undo segments roll around and reuse slots due to the high degree of COMMITs. Most important, the transaction entry for the original modification transaction is cycled over and reused. In addition, the system has reused undo segment extents,preventing a consistent read on the undo segment header block itself.

•\ Additionally, the lowest SCN recorded in the undo segment now exceeds t1 (it is higher than the read-consistent SCN of the query),due to the large number of commits.

When our query gets to the block that was modified and committed before it began, it is in trouble. Normally, it would go to the undo segment pointed to by the block and find the status of the transaction that modified it (in other words, it would find the COMMIT SCN of that transaction). If the COMMIT SCN is less than t1, our query can use this block. If the COMMIT SCN is greater than t1, our query must roll back that block. The problem is, however, that our query is unable to determine in this particular case if the COMMIT SCN of the block is greater than or less than t1. It is unsure as to whether it can use that block image or not. The ORA-01555 error then results.

To see this, we will create many blocks in a table that need to be cleaned out. We will then open a cursor on that table and allow many small transactions to take place against some other table—not the table we just updated and opened the cursor on. Finally, we will attempt to fetch the data for the cursor. Now, we know that the data required by the cursor will be “OK”—we should be able to see all of it since the modifications to the table would have taken place and been committed before we open the cursor. When we get an ORA-01555 error this time, it will be because of the previously described issue with delayed block cleanout. To set up for this example, we’ll use

•\ The 4MB UNDO_SMALL undo tablespace.
•\ A 300MB SGA, this is so we can get some dirty blocks flushed to disk to observe this phenomenon.

Note You might wonder why I didn’t use CASCADE=>TRUE on the gather statistics call to gather statistics on the index created by default by the primary key constraint. That is because a CREATE INDEX or ALTER INDEX REBUILD has implicit compute statistics added to it already whenever the table it is indexing is not empty. So, the very act of creating the index has the side effect of gathering statistics on itself. There’s no need to regather the statistics we already have.