Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: rollback space

Re: rollback space

From: Tom Dyess <>
Date: Thu, 3 Feb 2005 09:33:15 -0500
Message-ID: <NyqMd.24079$>

"Frank van Bortel" <> wrote in message news:cttbon$rb3$
> Tom Dyess wrote:
>> <> wrote in message
>>>1) Each row has 10 columns. Approximately 70 B each row.
>>>2) No lobs
>>>3)No other transactions are running, this is a batch load, any other
>>>transaction if running at all is select.
>>>4) there are no on isert triggers on the table.
>>>I beg to differ on commiting after every 'x' rows. I always prefer to
>>>commit once at the end of the transaction. Commiting often is
>>>definitely more work plus there is always the chance of the dreaded
>>>ORA-01555 "Snapshot too old" error.
>>>In my ETL procedures every insert and update is time stamped and hence
>>>it can be identified.
>> Here's a pretty good article on the "Snapshot too Old." I committing
>> every x rows shouldn't cause a problem. Concerning it being more work, it
>> shouldn't take too long, or at least you'll make up for it by having your
>> insertion work as desired. Have you tried Oracle's direct data loading?
>> I've used DOA for Delphi and it utilizes this capability.
> Did you *read* your own recommendation? It says (about committing,
> also called fetch across commit): "so a "snapshot too old" error becomes a
> real possibility."
> I dare to go further: it's a receipe for a snapshot too old!
> Commit once is a sound advice.
> --
> Regards,
> Frank van Bortel

Oh, you mean that italic part about 1/2 way down. Lol. Yes, I noticed it. I also noticed the next paragraph:

>As discussed earlier, increasing the number and size of your rollback
>segments also helps avoid the "snapshot too old" error message by reducing
>the >likelihood that a committed pre-image is overwritten, but it's
>difficult to predict how big they will need to be, and you may not want to
>create your rollback >segments that big anyway.

It seems his issue is his rollback segmets are huge and he isn't getting it to finish.

> I dare to go further: it's a receipe for a snapshot too old!

You're very daring, Frank. It's not a zero-sum game. I'm not talking about every single row, but 2 commits over the scope of the loading would be something to try. What's your solution?

Tom Dyess 
Received on Thu Feb 03 2005 - 08:33:15 CST

Original text of this message