Dropping Rollback Segments

Before dropping a rollback segment, make sure that status of the rollback segment is OFFLINE. If the rollback segment that you want to drop is currently ONLINE, PARTLY AVAILABLE, NEEDS RECOVERY, or INVALID, you cannot drop it. If the status is INVALID, the segment has already been dropped. Before you can drop it, you must take it offline.

To drop a rollback segment, you must have the DROP ROLLBACK SEGMENT system privilege.

If a rollback segment is offline, you can drop it using either the Drop menu item of Server Manager, or the SQL command DROP ROLLBACK SEGMENT.

The following statement drops the DATA1_RS rollback segment:

DROP PUBLIC ROLLBACK SEGMENT data1_rs;

If you use the DROP ROLLBACK SEGMENT command, indicate the correct type of rollback segment to drop, public or private, by including or omitting the PUBLIC keyword.

Note: If a rollback segment specified in ROLLBACK_SEGMENTS is dropped, make sure to edit the parameter files of the database to remove the name of the dropped rollback segment from the list in the ROLLBACK_SEGMENTS parameter. If this step is not performed before the next instance startup, startup fails because it cannot acquire the dropped rollback segment.

After a rollback segment is dropped, its status changes to INVALID. The next time a rollback segment is created, it takes the row vacated by a dropped rollback segment, if one is available, and the dropped rollback segment's row no longer appears in the DBA_ROLLBACK_SEGS view.

See Also: For more information about the view DBA_ROLLBACK_SEGS, see the Oracle7 Server Reference.