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

> >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".
> I would create a HUGE rollback segment. You don't say anything about the
> 2 million rows. What is the size of a single record.

        The size of the row is completely irrelevant for INSERT operations. The rollback segment only contains the data necessary to undo (rollback) the
database operation; for an INSERT the only data which is necessary to do this
is the rowid of the new rows.

        A "huge" rollback segment should not be necessary. I normally create rollback segments with 5mb extents, and an optimal size of 25mb. The tablespace
for your rollback segments ought to be fairly large in case extension *is*
required -- say, 400-600mb. However, insert operations, even when you are
inserting two million rows without any commits, shouldn't generate a lot of
rollback information ... *unless* the table in question is indexed.

        I therefore suggest that before you do the insert operation, you disable or drop any primary or unique keys on the table, and drop all other
indexes. After the insert is complete, rebuild the keys and indexes. Alternatively, split up the insert operation into several transactions by
inserting a COMMIT every, say, 50 thousand records.

        As far as I can remember, SQL*Loader using the direct path does not generate rollback information. It should therefore not be a problem.

        SELECT statements, since they do not change the database, do not generate rollback information. The query should also not be a problem.

HTH, Brett Neumeier Received on Fri Aug 15 1997 - 00:00:00 CDT