From: gsa@panix.com (Gary Assa)
Subject: Re: ORA-01562 message, WHO CAN SOLVE MY PROBLEM?
Date: 1997/08/08
Message-ID: <5sfcup$irv@panix.com>#1/1
References: <01bca35f$da1d38a0$044c12c3@t273862>
Organization: Panix
Newsgroups: comp.databases.oracle,comp.databases.oracle.marketplace,comp.databases.oracle.misc,comp.databases.oracle.server
X-Newsposter: trn 4.0-test55 (26 Feb 97)



>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. Without this info, I would
say to create a HUGE rollback segment of say, 500Mb, with the initial
segment being 50Mb, and NEXT being 50Mb, with MAXEXTENTS being 10. For the
insert, set transaction to this rollback segment. you can look up the
syntax in the reference books.

SQLLOAD should be committing after every X number of rows, and should not
really cause any problems in the Rollback segment tablespace. X should be
around 64.

A Query with a sort usually will cause a problem in ther TEMP tablespace and
not the Rollback tablespace. Are you doing a sub-select?

As for the ORA-01650, you ran out of space in the tablespace, or at least
contiguos space. You should add another datafile, or make you NEXT extent
size smaller.

hope this helps. Write back for more help.
-- 
                     ------------------------------------
If you have a condom and sunscreen SPF 15 or greater, than it's safe to look at
                     http://www.panix.com/~gsa/index.html