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:
Hi,
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?)
Regards
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.
HTH,
--
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Other related posts:
