Dropping Unused Object Storage

Beginning with Oracle Database 11g release 2 (11.2.0.2), the DBMS_SPACE_ADMIN package includes the DROP_EMPTY_SEGMENTS procedure, which enables you to drop segments for empty tables and partitions that have been migrated from previous releases. This includes segments of dependent objects of the table, such as index segments, where possible.

The following example drops empty segments from every table in the database.

BEGIN
  DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS();
END;

The following drops empty segments from the HR.EMPLOYEES table, including dependent objects.

BEGIN
  DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS(
    schema_name  => 'HR',
    table_name   => 'EMPLOYEES');
END;

This procedure requires 11.2 compatibility level.