Re: Rollback segment too small

In article <ff16ca4d8d7eb6aa6005dc16771139d2.43976_at_mygate.mailgate.org>, "Se.d.a." says...
>
>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.

but what you don't say is that you commit in a cursor for loop that is querying the tables you are modifying -- the number one way to get the 1555.

Best option:
remove the commit. do the work as a single unit.

second option:
don't hold cursors open across your commits.

third option:
PREALLOCATE your rollback segments larger -- set their minextents bigger. Avoid having the rollback wrap at all during your load (which leads us back to #1, remove the commit ;)

See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923

for details on the 1555.

>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
>
>
>--
>Posted from ppp-62-11-130-65.dialup.tiscali.it [62.11.130.65]
>via Mailgate.ORG Server - http://www.Mailgate.ORG

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp