Re: Rollback segment too small

On Tue, 20 Nov 2001 09:23:48 +0000 (UTC), "Se.d.a." <vdam73_at_yahoo.com> wrote:

>Hi,
>I created a stored Procedure in Oracle 8.1.7 wich imports about 20,000 records
>in several tables. The source table is another Oracle table.
>The SP executes insert into the destination tables, a delete from the source
>table and finally a commit for each record.
>The SP never reaches the end because an error occours: ORA-01555 snapshot too
>old: rollback segment number <n> with name <name> too small.
>I have tried to increase the number of rollback segments and the tablespace
>size but the number of records imported is approximatively the same.
>I tried also to remove the delete statement from the SP and it runs
>succesfully.
>Now I have 28 rollback segments and a tablespace of 128 MB. From the V$ROLLSTAT
>view I checked that the max dimension reached from all segments is about
>500-700 KB. The number of max extents is setted to 32760. Actually there are
>allocated 8-9 extents.
>
>Thanks for help.
>
>Amedeus
>Se.d.a. - Italy

ORA-1555 has been discussed at least a thousands of times. I'm not going to repeat the explanation here, you can find it in the archives of this newsgroup at http://groups.google.com and _please always_ search the archives _BEFORE_ posting.

You will find your _exact_ problems is you are committing _every_ record. You should either commit only once (preferred) or increase the minextents of all your rollback segments. Your are getting this error because data this is been kept in your rollback segment for read consistency purposes doesn't pertain to a transaction anymore (as you have committed), and can be overwritten. However your session still needs it for read consistency purposes. If you don't commit your rollback segments will grow (as the data in the rollback segment belong to a transaction, so the rollback segment is extendedd. Read-consistency data doesn't belong to a transaction).

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Nov 20 2001 - 12:29:14 CST