Dropping and recreating rollback segments to avoid error

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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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


This was first published in June 2003