DEALLOCATE clause

Purpose

Prerequisites

• ALTER CLUSTER

• ALTER TABLE

• ALTER INDEX

Syntax

Keywords and Parameters

KEEP

specifies the amount of unused space to keep.

integer the number of bytes to keep. You can also use K or M to specify the size in kilobytes or megabytes.

Usage Notes

You use the DEALLOCATE clause to reclaim unused space in extents in a cluster, table or index for reuse by other objects in the tablespace. The user quota for the tablespace in which the deallocation occurs is credited by the amount of the released space.

Unused space is deallocated from the end of the object toward the high water mark at the beginning of the object. 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.

INITIAL, MINEXTENTS and NEXT are described in the STORAGE clause .

If you omit the KEEP option and the high water mark is above the size of INITIAL and MINEXTENTS, then all unused space above the high water mark is freed. When the high water mark is less than the size of INITIAL or MINEXTENTS, then all unused space above MINEXTENTS is freed.

If you use the KEEP option, 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 MINEXTENTS is adjusted to the new number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is adjusted to the new size.

NEXT is set to the size of the last extent that was deallocated.

Example I

The following command frees all unused space for reuse in table EMP, where the high water mark is above MINEXTENTS:

ALTER TABLE emp
	DEALLOCATE UNUSED

Related Topics