Re: Rollback segments

"Kochis" <djkochis_at_sgi.net> writes:

>Trying to delete all the data from a table (6000 records)
>I recieved the following:
>Ora - 1562: Failed to extend rollback segments (Id=1)
>Ora - 1682: Max # extents (121) reached for rollback segment add_rb
>Did I run out of extents and I need to increase max?
>Did I run out of space in the rollback segment? (same as above?)
>Also,
>I have three rollback segments created and online. Oracle always seems to
>use the first one i created? Should I point it to a different one?
>thanks in advance for any help
>newbie
>djkochis_at_sgi.net

Your INITIAL and NEXT of the rollback segment "add_rb" is too small for what you need to do. The ORA-1682 that you received tells you that the OS-Dependent maximum number of extents (121 for your db_block_size) was reached. You cannot increase this number past the 121 mark.

What you can do is drop and recreate the rollback segment with a much larger INITIAL and NEXT (100 times the size should be fair). You will then need to monitor the rollback segments again to see if the new size is sufficient. You don't know at this point at which record of the 6000 it failed, and you may one day need to delete more than 6000 records.

As for your second question, Oracle should pick different rollback segments after each commit or rollback. Double-check that you are specifying the rollback names in the init.ora/config.ora file, and that the rollback segments are on-line. Aside from that, the user/application would have had to specificly set the transaction to use the same rollback segment each time in order to get the effects you described above.

Hope this helps....

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->