deallocate_unused_clause to explicitly deallocate unused space at the end of a database object segment and make the space available for other segments in the tablespace.
You can deallocate unused space using the following statements:
CLUSTER (see ALTER CLUSTER
INDEX: to deallocate unused space from the index, an index partition, or an index subpartition (see ALTER INDEX
VIEW: to deallocate unused space from the overflow segment of an index-organized materialized view (see ALTER MATERIALIZED VIEW)
TABLE: to deallocate unused space from the table, a table partition, a table subpartition, the mapping table of an index-organized table, the overflow segment of an index-organized table, or a LOB storage segment (see ALTER TABLE
This section describes the semantics of the
deallocate_unused_clause. For additional information, refer to the SQL statement in which you set or reset this clause for a particular database object.
You cannot specify both the
deallocate_unused_clause and the
allocate_extent_clause in the same statement.
Oracle Database frees only unused space above the high water mark (the point beyond which database blocks have not yet been formatted to receive data). Oracle deallocates unused space beginning from the end of the object and moving toward the beginning of the object to the high water mark.
If an extent is completely contained in the deallocation, then the whole extent is freed for reuse. If an extent is partially contained in the deallocation, then the used part up to the high water mark becomes the extent, and the remaining unused space is freed for reuse.
Oracle credits the amount of the released space to the user quota for the tablespace in which the deallocation occurs.
The exact amount of space freed depends on the values of the
NEXT storage parameters. Refer to the storage_clause for a description of these parameters.
Specify the number of bytes above the high water mark that the segment of the database object is to have after deallocation.
If you omit
KEEP and the high water mark is above the size of
MINEXTENTS, then all unused space above the high water mark is freed. When the high water mark is less than the size of
MINEXTENTS, then all unused space above
MINEXTENTS is freed.
If you specify
KEEP, then the specified amount of space is kept and the remaining space is freed. When the remaining number of extents is less than
MINEXTENTS, then Oracle adjusts
MINEXTENTS to the new number of extents. If the initial extent becomes smaller than
INITIAL, then Oracle adjusts
INITIAL to the new size.
In either case, Oracle sets the value of the
NEXT storage parameter to the size of the last extent that was deallocated.