crash with big data loads

[Joseph J Frantiska]

When one of the SQL programmers in our group tries to load a lot of
records (approx. 130K), only 124K are loaded. The error ORA-01555
"snapshot too old (rollback segment too small)" appears.In accordance
with Loney's book "ORACLE DBA Handbook", the RBS_2 tablespace was created
with a large datafile andlarge rollback segments esclusively for large data
loads. However, the problem continues when anymore than 124K records are
loaded. Any suggestions?????

[Andrew Zitelli]

jfra...@titan.oit.umass.edu (Joseph J Frantiska) wrote:
> When one of the SQL programmers in our group tries to load a lot of
> records (approx. 130K), only 124K are loaded. The error ORA-01555
> "snapshot too old (rollback segment too small)" appears.In accordance
> with Loney's book "ORACLE DBA Handbook", the RBS_2 tablespace was

> created with a large datafile andlarge rollback segments exclusively

> for large data loads. However, the problem continues when anymore than
> 124K records are loaded. Any suggestions?????

Have you verified the new rollback segment is the one being used
by your large transactions? I assume you are executing the command:

SET TRANSACTION USE ROLLBACK SEGMENT rbs_2

We have a similar situation for large data loads (1,000,000+ records)
being inserted as a single transaction from Pro*C. The
SET TRANSACTION command must be executed at the start of a
transaction for the designated rollback segment to be used. To avoid
the possibility of a transaction already being in progress when we
execute a SET TRANSACTION command, we explicitly execute a COMMIT
or ROLLBACK just prior to the SET TRANSACTION command. In our
environment, we found that stored procedures and triggers which we did
not expect to start a transaction, were in actuality starting
a transaction. I hope this is useful in tracking down your problem.

-- Andy Zitelli

[nso...@acay.com.au]

Any suggestions?????
>
A few. You may be loading into a table that already has indexes defined.
This means your space calculations for RLB may be off by the index amount.
Also, if you have concurrent access to this table while loading, you're bound
to get this message.

Try doing this at an off-line time and dropping all indexes before the big load
and re-creating them at the end.

Also, try to commit every few thousand rows loaded to reduce the strain on
the RLBsegs, if this is possible in the logic of your loads.

HTH.