Database Reference
In-Depth Information
That
UPDATE
used 151 blocks to store its undo. The commit would free that up, or release it, so if we rerun the
query against
V$TRANSACTION
, it would once again show us
no rows selected
. When we update the same data—only
indexed this time—we'll observe the following:
EODA@ORA12CR1> update t set indexed = lower(indexed);
72077 rows updated.
EODA@ORA12CR1> select used_ublk
2 from v$transaction
3 where addr = (select taddr
4 from v$session
5 where sid = (select sid
6 from v$mystat
7 where rownum = 1
8 )
9 )
10 /
USED_UBLK
----------
854
As you can see, updating that indexed column in this example generated several times as much undo. This
is due to the inherit complexity of the index structure itself and the fact that we updated every single row in the
table—moving every single index key value in this structure.
ORA-01555: Snapshot Too Old Error
In Chapter 5, 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.
■
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.
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 5.
•
Block cleanout.
Search WWH ::
Custom Search