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 -> Re: Can Updates with subselect cause ORA-1562 rollback errors. DBA says he has no idea of cause.

Re: Can Updates with subselect cause ORA-1562 rollback errors. DBA says he has no idea of cause.

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Tue, 24 Jun 2003 17:21:57 -0700
Message-ID: <3EF8EB25.4D1DBC77@exxesolutions.com>


johnston wrote:

> We are experieincing a critcal problem with a production app. We are getting
>
> ORA-01562: failed to extend rollback segment number 8
> ORA-01650: unable to extend rollback segment R07 by 128 in tablespace RBS
>
> which causes our app to fail.
>
> We are using Oracle 9i 9.2.0.3.0 on · SUNFIRE E15K with
> 8 - 900Mhz CPU's 32GB memory and 900 gigs of disk running SunOS 5.8
>
> We have a rollback tablespace of 1 gig with 10 rollback segments: all 1 meg
> in size with 1 meg extents with max extents at 32000, DBA says they can each
> grow as needed to max size of 1 gig (not all 10 but a single one can grow to
> 100s of megs if needed)
>
> We have 2 tables Small and BIG
>
> Small has 7000 rows and is 1.5 megs in size
>
> BIG has 1,800,000 rows and is 120 megs in size
>
> We have a procedure DO_FORECAST that calls 8 other procedures. It first
> writes a row to log table and commits it, then does some small selects,
> determines some values and then passes them to each procedure as it is called.
> finally it writes another row to a log table and commits it.
>
> Each of the called procedures opens cursors does some work staring with a
> writing a row to log table and commits it and does deletes, updates, inserts,
> etc, commits that work, writes to log table commits that and then passes back
> to DO_FORECAST.
>
> 1st called procedure updates BIG and then commits then passes control back to
> DO_FORECAST
>
> Next procedure that is called, the one that is failing with ORA-01562 errors
> does the following:
>
> Update SMALL set column a = (select X from BIG where …). We are updating all
> 7000 rows in small. But only doing a select against BIG. Yet we get the
> rollback error. Why is it happening when we have 1 gig and BIG is only 120
> mb.
>
> Is it possible that because DO_Forecast calls BIG in procedure 1 and updates
> and then it is selected in next procedure that somehow Oracle is trying to
> maintain a read consistent image of BIG (should not since it is not being
> written to) in 2nd procedure. According to Tom Kyte in his book selects do
> not cause rollbacks segments to grow, so it can not fit BIG in the 1 meg
> intial size???
>
> Please help.

Lots of things are possible. But what should happen is as follows:

CREATE UNDO TABLESPACE undotbs1
DATAFILE '<data_file_path_and_name>'
SIZE <as_big_as_you_can> REUSE AUTOEXTEND ON;

Then change the init.ora to drop all references to rollback and rollback segments and add:

undo_management=AUTO
undo_retention=<longer_than_your_longest_transaction_in_seconds>
undo_tablespace=UNDOTBS1

Then recreate the spfile:

CREATE SPFILE FROM pfile='init<SID>.ora';

In 9.2 you should be using UNDO, not ROLLBACK.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Tue Jun 24 2003 - 19:21:57 CDT

Original text of this message

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