Re: ORA-01562: ...failed to extend rollback segment.. when update/delete stmt issued...

Zareh Dergevorkian wrote:
>
> When issueing an UPDATE or DELETE SQL statement to an Oracle WGS2000 NT
> server, I get the error message ORA-01562: ...failed to extend rollback
> segment..
>
> I have allocated upto 200MB of rollback against a 250MB user_data name
> space...
>
> the statements that generate the error are obviousely updating/deleting more
> than what can be held in the RB segment... but what I'm wondering is why when
> the RB seg is almost as large as the entire database itself, and the
> statements only affect roughly 50+K recs (out of 1.5M+ recs), do I get an
> error failed to extend rb seg... ??????

We have a similar situation with rollback segments in our database with some very large, long-running batch jobs. The batch jobs generate up to 200MB of rollback on some databases.  We have increased our RBS tablespace size and the extent size of our rollback segments to get past the ORA-01562 problem.

What affect does a large running batch job have on your redo logs and recoverability? Both in running in ARCHIVELOG and NOARCHIVELOG modes. For example, what if you only have 3 20MB redo logs? Your batch job will trounce on the redo logs several times during a run. I would guess this hurts the ability for the job to rollback to savepoints, for example? And you probably can't recover either, or else it would take forever. Anyone have thoughts on this?

Thanks,
Brian

-- 
Brian M. Biggs                             mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.                       voice: (513) 677-7661
http://www.cincom.com/