Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
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.
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