Database Reference
In-Depth Information
■
ORA-01555
is not related to data corruption or data loss at all. It is a “safe” error in that regard; the only out-
come is that the query that received this error is unable to continue processing.
Note
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.
COMMIT
s (actually a variation on the preceding point). We covered
•
Your programs fetch across
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:
update t set x = 5 where x = 2;
insert into t select * from t where x = 2;
delete from t where x = 2;
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.
■
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.
Note
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
UPDATE
s them). The
INSERT
reads the table to find rows where
X=2
, and then
INSERT
s 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:
UNDO_RETENTION
properly (larger than the amount of time it takes to execute
your longest-running transaction).
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
.
•
Set the parameter
•
Increase the size of or add more undo segments when using manual undo management.
This decreases the likelihood of undo data being overwritten during the course of your long-
running query. This method goes toward solving all three of the previous points.
Note that this
is definitely not the preferred method; automatic undo management is highly recommended.
