Re: ORA-01555 help needed

Saqib Zulfiqar wrote:
>
> Hi All,
> I have written procedure which reads data from one table and inserts
> it in another table. When a row fails insertion, another procedure is
> called which inserts the rejected row in the rejected_rows table and
> then commits the transaction. Since the main procedure inserts 50 to
> 60 thousand rows, I therefore commit after every 500 inserts.
> Some of the times when I run the process I get the error message.
>
> ORA-01555: snapshot too old: rollback segment number 5 with name "R04"
> to small.
>
> I have studied the messages manual. It says that either your rollback
> segments are too small or you are using too many inserts plus rows are
> being fetched after a commit in the database.
> My rollback segment size is 30Mb, and since I am committing after 500
> rows, the rollback segment hardly grows at all. The other things is
> that I have to commit after a certain no. of rows, so that I may know
> that the procedure is running smoothly.
> Now the problem is that this error occurs sometimes, not most of the
> times, that means when the server is on heavy load, then the
> possibility of receiving this error is maximum.
> I have studied a bit about fetch across commits, but need a little bit
> more elucidation. Moreover if anybody has any ideas to handle this
> position and avoid this error, then please do respond !.
>
> Thanks,
> ////////////////////////////////////////////
> // //
> // Saqib Zulfiqar //
> // Software Engineer //
> // CresSoft Pvt Ltd. //
> // Lahore, Pakistan. //
> //E-Mail :saqib.zulfiqar_at_cressoft.com.pk //
> // //
> ////////////////////////////////////////////
>
> I get melancholic sometimes, but I've learned to enjoy it

Your rollback segment may be also used by another session(s) or someone try to access that table when you are inserting the data. Then the rollback segment will continue to serve that session even you issue the commit statement.

Joe Received on Wed Jun 04 1997 - 00:00:00 CDT