Undo Usage and Read consistency - ORA-1555

From: Guillermo Alan Bort <cicciuxdba_at_gmail.com>
Date: Tue, 14 Jul 2009 10:24:00 -0300
Message-ID: <172762180907140624ka2c37fenc1681770a353675_at_mail.gmail.com>

Dear DBAs,

We got into a discussion about how read consistency is implemented in Oracle and was wondering what you know of this.

The two sides are the following:
1) Undo is used for any and all selects, so if you do a full scan on a large table it is bound to give ORA-1555 even if there is no transaction modifying the table
2) Undo is only used when a transaction modifies data (DML) and ONLY then ORA-1555 is possible, since it happens when the consistent version of the block stored in the UNDO by the transaction ages out.

Documentation is unclear to this respect: From the concepts guide:

To manage the multiversion consistency model, Oracle must create a read-consistent set of data when a table is queried (read) and simultaneously updated (written). When an update occurs, the original data values changed by the update are recorded in the database undo records. As long as this update remains part of an uncommitted transaction, any user that later queries the modified data views the original data values. Oracle uses current information in the system global area and information in the undo records to construct a read-consistent view of a table's data for a query.

Does this mean that every time I perform a select I get a copy of the data into de undo?

Alan Bort
Oracle Certified Professional

Received on Tue Jul 14 2009 - 08:24:00 CDT

Original text of this message