I am running a delete statement, but it fails every time with a "cannot extend rollback segment..." error. The database has only one dml running at this time. The rollback segment is sized at 1250 MB. The number of records to be deleted is large, but definitely not more than 700 MB. Before running the delete command, I had manually run "alter database rollback segment rb1 shrink," and then checked the used space in the RBS tablespace, and it was 0. After a few hours when it failed, I again checked the used space in RBS tablespace, and it was 100%. Does it mean that though the record size is 700 MB, even then it has occupied 1.25 GB space, or there is something that has missed my notice?
The amount of rollback space required to delete data is not necessarily equivalent to the amount of data being deleted. There is lots of overhead information, for each row of data to be deleted. I've seen the rollback space requirements to be double the amount of data to be deleted. It all depends on the size of the row, and the data inside the row. So you may have to increase your RBS tablespace and let your rollback segments extend to hold this information.
Another method is to move all of the data to a temporary table, then truncate the original table, and copy back only those rows you want to keep. It can be a quicker operation too. But the data in the original table will be unusable to the application until the process is complete.
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.