Re: ORA-01562 message, WHO CAN SOLVE MY PROBLEM?

Jouke Wilkens wrote:

> We use ORACLE vs.7.1.
>
> 3 jobs were running at the same time:
> a. a job inserting 2 million rows in a table
> b. a direct path SQLLOAD
> c. a large query of 2 tables, resulting in a flat file
> after a sort.
>
> In all the 3 jobs was the error "ORA-01562: failed to extend rollback
> segment (id=1)"
> Job a. noticed on the next line "ORA-01650: unable to extend rollback
> segment ROLLBACK2 by 3839 in tablespace ROLLB".
>
> 1. Who can explain me what these errors mean?
> 2. In which situations does this error appear?
> 3. What are the most likely solutions to this?
>
> Thank you for your reaction!
>
> Jouke Wilkens
> The Netherland.

  The message means what it means: the server can't extend the rollback segment it uses. The job which causes it is most probably inserting rows (if you don't commit after some portions of them). Bot direct path load and select don't write anything into rollback segments, but selects use rollback segment to ensure read consitensy (so long-running selects require rollback segments to be large enough to keep the needed data). In your case you need to have at least one big rollback segment (you may asign it to your transaction with the command SET TRANSACTION USE ROLLBACK ...).

--
Vitaly Sikolenko                  Senior Sales Consultant
                                  Server & Gateways
ORACLE C.I.S.
Moscow Russia                      E-mail: vsikolen_at_ru.oracle.com

The statements and opinions expressed here are my own and do not
necessarily represent those of Oracle Corporation.