Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E10592-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

deallocate_unused_clause

Purpose

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:

Syntax

deallocate_unused_clause::=

Description of deallocate_unused_clause.gif follows
Description of the illustration deallocate_unused_clause.gif

(size_clause::=)

Semantics

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 INITIAL, MINEXTENTS, and NEXT storage parameters. Refer to the 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.