Re: crash with big data loads

From: Andrew Zitelli <zitelli_at_tus.ssi1.com>
Date: 1995/12/28
Message-ID: <4bur61$6vr_at_atlas.tus.ssi1.com>#1/1


jfrantis_at_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
Received on Thu Dec 28 1995 - 00:00:00 CET

Original text of this message