Database Reference
In-Depth Information
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
----------
151
EODA@ORA12CR1> commit;
Commit complete.
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 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.
