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:
ALTER ROLLBACK SEGMENT r01 STORAGE (MAXEXTENTS 300);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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.