Oracle7 Server SQL Reference

DROP ROLLBACK SEGMENT

Purpose

To remove a rollback segment from the database.

Prerequisites

You must have DROP ROLLBACK SEGMENT system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the rollback segment's creation label or you must satisfy one of the following criteria:

If the rollback segment's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the rollback segment's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the rollback segment's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

rollback_segment

is the name the rollback segment to be dropped.

Usage Notes

When you drop a rollback segment, all space allocated to the rollback segment returns to the tablespace.

You can only drop a rollback segment that is offline. To determine whether a rollback segment is offline, query the data dictionary view DBA_ROLLBACK_SEGS. Offline rollback segments have the value 'AVAILABLE' in the STATUS column. You can take a rollback segment offline with the OFFLINE option of the ALTER ROLLBACK SEGMENT command.

You cannot drop the SYSTEM rollback segment.

Example

The following statement drops the rollback segment ACCOUNTING:

DROP ROLLBACK SEGMENT accounting