Re: rollback segment errors

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Sun, 29 Sep 2002 21:23:46 +0200
Message-ID: <hkjepus9h5m71uuvsiumd88qobp2mb0i0b@4ax.com>

On 29 Sep 2002 07:07:18 -0700, sasubram_at_cisco.com (shankar) wrote:

>Hello,
>We are getting this error while I run a plsql process (just to update
>20 records).
>ORA-01555: snapshot too old: rollback segment number 1 with name "R11"
>too small

(...)
>Any idea/suggestion of what might be happening..
>
>According the dba, there is nothing wierd going in the db. We do have
>siebel server running on this db. I don't know whether siebel (which I
>hate) is causing problem.
>
>thanks in advance for your help
>
>shankar

Your outer select loop is apparently requesting data from a block that your process has altered earlier. Oracle tries to retrieve the unaltered block from rollback, but these data have been overwritten by your own updates. They are only protected from being overwritten as long as the related update is not committed: apparently you are committing before your outer select is finished.

What could help:

don't commit in between, and/or
make your rollback segment big enough, or
if you are the only process updating the database: if functionally possible add an 'ORDER BY ROWID' to your select. In this way Oracle will sort the rows of your select in the temporary tablespace before starting the updates (so the temporary tablespace contains all the unaltered blocks) and read them from there, so Oracle doesn't need to read from rollback to reconstruct altered blocks.

I used to use the latter method in an OLTP-application when doing big conversions for a new release.

Jaap. Received on Sun Sep 29 2002 - 14:23:46 CDT