In the last chapter, we briefly investigated the ORA-01555 error and looked at one cause of it: committing too frequently. Here, we’ll take a much more detailed look at the causes and solutions for the ORA-01555 error. ORA-01555 is one of those errors that confound people. It is the foundation for many myths, inaccuracies, and suppositions.

Note ORA-01555 is not related to data corruption or data loss at all. It is a “safe” error in that regard; the only outcome is that the query that received this error is unable to continue processing.

The error is actually straightforward and has only two real causes, but since there’s a special case of one of them that happens so frequently, I’ll say that there are three:

•\ The undo segments are too small for the work you perform on your system.
•\ Your programs fetch across COMMITs (actually a variation on the preceding point). We covered this in Chapter 8.
•\ Block cleanout.

The first two points are directly related to Oracle’s read consistency model. As you recall from Chapter 7, the results of your query are preordained, meaning they are well defined before Oracle goes to retrieve even the first row. Oracle provides this consistent point-in-time “snapshot” of the database by using the undo segments to roll back blocks that have changed since your query began. Every statement you execute, such as the following

SQL> update t set x = 5 where x = 2;
SQL> insert into t select * from t where x = 2;
SQL> delete from t where x = 2;
SQL> select * from t where x = 2;

will see a read-consistent view of T and the set of rows where X=2, regardless of any other concurrent activity in the database.

Note The four statements presented here are just examples of the types of statements that would see a read-consistent view of T. They are not meant to be run as a single transaction in the database, as the first update would cause the following three statements to see no records. They are purely illustrative.

All statements that “read” the table take advantage of this read consistency. In the example just shown, the UPDATE reads the table to find rows where x=2 (and then UPDATEs them). The INSERT reads the table to find rows where X=2, and then INSERTs them, and so on. It is this dual use of the undo segments, both to roll back failed transactions and to provide for read consistency that results in the ORA-01555 error.

The third item in the previous list is a more insidious cause of ORA-01555 in that it can happen in a database where there is a single session, and this session is not modifying the tables that are being queried when the ORA-01555 error is raised! This doesn’t seem possible—why would we need undo data for a table we can guarantee is not being modified? We’ll find out shortly.

Before we take a look at all three cases with illustrations, I’d like to share with you the solutions to the ORA-01555 error, in general:

•\ If your undo tablespace has autoextend enabled, set the parameter UNDO_RETENTION properly (larger than the amount of time it takes to execute your longest-running transaction or the longest expected flashback operation). V$UNDOSTAT can be used to determine the duration of your long-running queries. Also, ensure sufficient space on disk has been set aside so the undo segments are allowed to grow to the size they need to be based on the requested UNDO_RETENTION.

•\ If your undo tablespace is a fixed size, then the UNDO_RETENTION parameter is ignored, and Oracle automatically tunes undo. For fixed-size undo tablespaces, choose a size sufficiently large enough to accommodate long-running queries and flashback operations.

•\ Reduce the runtime of your query (tune it). This is always a good thing if possible, so it might be the first thing you try. It reduces the need for larger undo segments. This method goes toward solving the previous points.

•\ Gather statistics on related objects. This helps avoid the block cleanout point listed earlier. Since the block cleanout is the result of a very large mass UPDATE or INSERT, statistics gathering needs to be done anyway after a mass UPDATE or large load.

We’ll come back to these solutions, as they are important to know. It seemed appropriate to display them prominently before we begin.