Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: Rollback segments
References: <01bbe786$9e7014c0$79b597ce@orion.sgi.net.sgi.net>
"Kochis" 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@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: www.arikaplan.com <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page