Re: Rollback segment too small

Damiano 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

First thing to do would be the reduce the number of commits (preferably to zero) in your stored procedure. Commits within a loop are the number one cause of ORA-1555.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."