Re: Fwd: Snapshot too old from READ-ONLY table (data pump export)

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: jan.hendrik.boll@xxxxxxxxxxxxxx
  • 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 <>
Konagora <>
RoughSea Channel on Youtube <>

Other related posts: