Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF



Use the 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:



Text description of clauses19.gif follows
Text description of deallocate_unused_clause


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 frees only unused space above the high water mark (that is, 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 INITIAL, MINEXTENTS, and NEXT storage parameters.

See Also:

storage_clause for a description of these parameters

KEEP integer

Specify the number of bytes above the high water mark that the segment of the database object is to have after deallocation.