Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> ORA-01650 Issues with rollback segments for batch jobs

ORA-01650 Issues with rollback segments for batch jobs

From: SL <sebastien-louchart_at_wanadoo.fr>
Date: 6 Apr 2006 02:31:23 -0700
Message-ID: <1144315883.203138.222030@i40g2000cwc.googlegroups.com>


Hello,

I'm running a 8.1.7.4 DB on a Solaris 8 node.

During the execution of a batch job (a cleansing procedure issuing DELETE on large tables), I got the following error: ORA-01562: failed to extend rollback segment number 2 ORA-01650: unable to extend rollback segment <rbs_name> by 4096 in tablespace <ts_name>

I got this error several times and, for instance, I can't figure why : The tablespace is 11 Gb large, the HWM size of the RBS is only 4 Gb and maxtextents wasn't reached this time.

Here are the settings for the TS intended to hold RBS for batch jobs (manually affected to tx by a set transaction statement)

6 files (5*2047M + 1*1024M)
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16384K Here are the most recent stats for the RBS from v$rollstat view

       USN EXTENTS RSSIZE WRITES GETS WAITS OPTSIZE HWMSIZE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

         2 128 2147479552 4065483692 7996346 3249 -214748364 4294959104

SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE ---------- ---------- ---------- ---------- ----------   51 655 570 82241174 1131617937 On a precedent run, this RBS went into saturation I analyzed that it's reached its maxextents (sum wraps+extents > 32764) v$rollstat for that run:

NAME                              HWMSIZE  AVEACTIVE    OPTSIZE
SHRINKS  AVESHRINK      WRAPS    EXTENDS
------------------------------ ---------- ---------- ----------
---------- ---------- ---------- ----------
<rbs name>                     4294930432   35675355  536870912
1536    2088197      17417      15347

I changed the settings of the TS, increasing the extent size to 16M.

My goal was to create a rollback segment large enough to hold to larger transaction the job issues (approx 5 Gb)

I can't explain why the OPTIMAL size is a negative number, it seems to be a limit at 2047 Mb which may be related to the max size of the underlying files (OS limit).

My questions are :
1) Why do I get these extensions errors ? 2) Why can't I set the optimal size of the rbs to the desired value ?

Any help would be appreciated.

-- 
Sebastien LOUCHART
Received on Thu Apr 06 2006 - 04:31:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US