Dropping and recreating rollback segments to avoid error

I am using Oracle 7.1.6 on SCO Unix 5. I want to delete a large number of rows from a particular table, but when

I gave the delete command, I got the error "Maxium extents reached for rollback segment so and so...." I checked and found that out of four rollback segments, three have reached their maximum extents limit i.e. 249. Can I drop the rollback segments and recreate them to avoid this error? If yes, how?

You can drop the rollback segments, but I suspect that it won't solve your error. When you issue any DELETE command, it logs undo information in the rollback segments. After the DELETE statement has finished processing, you have the option of committing your work or rolling it back. If you choose to rollback, then the system gets the rollback information from the rollback segments.

You have a few options. One, you can delete in smaller amounts. For instance, try to see if you can delete only 25% of the rows you want to delete. Then, issue a COMMIT and delete the next 25% of the rows. Repeat until all of the rows you want to delete have been removed.

Two, you can increase your rollback segments growth. This can be done with a command similar to the following:

Above, I chose a number higher than the '249' in your error message. You can choose a higher number too. Keep in mind that the tablespace that holds the rollback segment must be large enough to hold the larger rollback segment.

A third option is to use the TRUNCATE command instead of the DELETE statement. With TRUNCATE, you will be removing all rows of the table. So if you wan't to keep a few of the rows, the TRUNCATE command is not for you. The TRUNCATE command has a "benefit" that it does not use the rollback segments, and it is faster. But you cannot undo the operation. Once you hit the ENTER key, the TRUNCATE command cannot be undone.

For More Information

This was first published in June 2003

Dig deeper on Oracle database design and architecture



Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: