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

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. Received on Tue Jun 24 2003 - 17:10:21 CDT