Re: Fwd: Snapshot too old from READ-ONLY table (data pump export)
Date: Tue, 19 Jul 2011 12:59:51 +0200
On 07/19/2011 09:47 AM, Jan-Hendrik Boll wrote:
> I have one question about what happens after the ORA-01555:
> If i am getting it correctly: the information stored in the
> block-header says: uncommited changes, oracle tries to read the
> information from undo which leads us to ORA-01555.
> And what happens next to our changed block?
> Is oracle perfoming a rollback on the uncommited blocks? (Wouldn't
> that lead to ORA-01555 too?)
> Jan-Hendrik Boll
ORA-01555 isn't a problem due to uncommitted data. It's linked to committed data. What happens is that as long as a change is uncommitted, then the previous value is kept in rollback segments (undo) - as long as you don't run out of undo space, everything is fine. Trouble starts when a change is committed, because then Oracle doesn't feel compelled to keep previous values. It will try to keep them as long as possible, but not forever. When a change is committed, you can no longer rollback; and from then on previous values can only be needed for giving a consistent view to a concurrent query that would have started before the transaction was committed (and therefore at a time when it couldn't see the change) and that must be kept in a blissful ignorance of the change until it has returned its very last row (this is the basic principle of read-consistency in Oracle - whatever the duration of a query, it must "see", as long as it runs, the database in the very same state as it could see it when it started running). I am ignoring flashback queries here, but you could extend the reasoning. It's a long query that runs while many, many changes occur AND ARE COMMITTED that has a problem and gets ORA-01555, because if a large number of changes requires massive amounts of undo, Oracle will happily reuse undo space of transactions that have been committed, even if a SELECT needs the data for consistency. The SELECT will abort, and hopefully if you run it again , if it had the mishap of running during a burst of changes to the database, it will complete successfully. If not, review your code.
-- Stephane Faroult RoughSea Ltd <http://www.roughsea.com> Konagora <http://www.konagora.com> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 19 2011 - 05:59:51 CDT