Problem solve Get help with specific problems with your technologies, process and projects.

Rollback space required to delete records

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

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.