Re: rollback space

"Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message news:cttbon$rb3$1_at_news3.zwoll1.ov.home.nl...
> Tom Dyess wrote:
>> <manish.nevrekar_at_gmail.com> wrote in message
>> news:1107369583.318529.264990_at_z14g2000cwz.googlegroups.com...
>>
>>>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.
>>>Thanks
>>>Manish
>>>
>>
>>
>> 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.
>>
>> http://www.oraclepower.com/WebPortal/webportal?aid=sr&id=11337
>>
>
> 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
OraclePower.com