The importance of DBA_ROLLBACK_SEGS – 1 of 2

Unless you have been a DBA for more than 10 years, you might not have even heard of DBA_ROLLBACK_SEGS. Back in the days of Oracle6 and Oracle7, when there was no Interwebs and every kid had to walk to school uphill both ways in the snow, Oracle had rollback segments.

Before we had undo tablespaces, DBA’s had to create rollback segments manually, specifying storage parameters, and adding them to the init.ora. Yes, we had init.ora files which had to edit manually – we didn’t have those fancy ALTER SYSTEM commands to change parameters. Why is this view referencing a component of Oracle that has been obsolete for over 10 years important now?

Try querying DBA_ROLLBACK_SEGS in a modern database and you will get results like:


What is the significance of these results? Well, all undo tablespaces are is an automagically managed version of rollback segments. The underlying technology isn’t really different. The significance is when your undo tablespace is for whatever reason, unavailable and you can’t open your database. Also for whatever reason, you don’t have good backups of your undo tablespace. We know everyone always has good backups of their undo tablespaces but in case you don’t and can’t open your database, Oracle has an undocumented parameter:


This parameter can be used to force the database open if rollback segments (undo tablespaces) are missing or corrupted. It is potentially nasty, as it allows the database to open by assuming every transaction in the rollback segments is a complete, committed transaction. This can lead to logical corruption throughout the database, and you would need to take an export and rebuild the database afterwards. That is still better then losing all of your data.

The problem is, _corrupted_rollback_segments requires the list of the segment_names. If you can’t open your database, how do you get the list? In the olden days, your init.ora might help you, but with these fancy new undo tablespaces there is no parameter to help. You need to have queried dba_rollback_segments before you had the failure.

The point of this blog is not to advocate using _corrupted_rollback_segments. Instead it is to put the query in your backup scripts. You probably won’t need it, but if you do, you will be very happy you have it.

Comments are closed.