Deallocate Unused Space
Go Up to Available object actions by DBMS
The Deallocate Unused Space dialog lets you deallocate space from clusters, indexes, and tables. You can also deallocate unused space from table partitions and subpartitions. When you find that allocated space is not being used, you can free that space for use by other objects by explicitly deallocating space. Oracle releases the freed space to the user quota for the tablespace in which the deallocation occurs.
Oracle deallocates unused space from the end of the object toward the high water mark. In other words, Oracle frees space starting with the space that would have been used last. If an extent is completely contained in the space to be deallocated, then the whole extent is freed. If an extent is only partially contained in the space to be deallocated, then Oracle shrinks that extent to the size of the used space up to the high water mark, and frees the unused space in the extent.
If you are deallocating unused space from and index and the index is range-partitioned or hash-partitioned, Oracle deallocates unused space from each partition in the index. If an index is a local index on a composite-partitioned table, Oracle deallocates unused space from each of the subpartitions in the index.
- Tip: You can verify that the deallocated space is freed by going to the Space tab in the appropriate editor.
Platform Availability
To Deallocate Unused Space from an Index or Table
- Initiate a Deallocate Unused Space action against one or more Clusters, Indexes, Primary Keys, Tables, or Unique Keys. For more information see Initiating an object operation.
- The Deallocate Unused Space dialog opens.
- Use the following table as a guide to understanding and specifying options on this dialog:
| Option | Description |
|---|---|
|
Specify the number of bytes above the high-water mark that the objects will have after deallocation. If no value is specified, all unused space will be freed. |
If you do not specify an amount of unused space and the high water mark is above the size of INITIAL and MINEXTENTS, then all of the unused space is freed. If the high water mark is less than the size of INITIAL or MINEXTENTS, then all unused space above MINEXTENTS is freed. If you specify an amount of unused space and the remaining number of extents is smaller than MINEXTENTS, then the MINEXTENTS value changes to reflect the new number. If the initial extent becomes smaller as a result of the deallocation, the INITIAL value changes to reflect the new size of the initial extent. |
- 3. Click Execute. For information on the scheduling option, see Scheduling.