Re: failed to extend rollback segment

Another thing to try is to commit more often. If you are doing alot of inserts/updates, can you commit, say, every 400 to 500 transactions so that it doesn't fill up the rollback segment that it is using. Try using PL/SQL with a for...loop to commit every 400 transactions. For instance, If you have 100,000 transactions to run, why not commit more often,instead of letting the rollback segment fill up? Do you need to rollback all of those transactions if something fails? Probably not, so commit more often and the problem will go away. ORA-01562 is a major symptom of this problem. I don't know the whole situation, but I hope this helps. Let me know if you are having anymore problems.

Rob Calfee
DBA
l_robert_c_at_yahoo.com

<rkegel_at_my-deja.com> wrote in message news:832olh$1l6$1_at_nnrp1.deja.com...
> Hi,
>
> I ran several insert/update statements on very large tables (using
> Oracle on Win NT) and encountered the problem of too small rollback
> segements for some of these statements, e.g.:
>
> ORA-01562: failed to extend rollback segment number 3
> ORA-01650: unable to extend rollback segment RS3 by 512 in tablespace
> RBS
>
> or
>
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01562: failed to extend rollback segment number 5
> ORA-01650: unable to extend rollback segment RLARGE by 1280 in
> tablespace RBS
>
> I read that I can specifically set a rollback segment for a certain
> statement with
>
> set transaction use rollback segment xyz
>
> But how can I find out what size and max. number I should specify?
> Is there a problem when I set a rollback segement too large?
> What else do I have to take into account?
>
> Thank you for your help.
> Rainer
> ---
> Rainer Kegel
> Scheuring Projektmanagement
> http://www.scheuring.ch
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Dec 13 1999 - 11:34:23 CST