It is not uncommon to allocate space to a segment, only to find out later that it is not being used. For example, you may set PCTINCREASE to a high value, which could create a large extent that is only partially used. Or you could explicitly overallocate space by issuing the ALTER TABLE ALLOCATE EXTENT statement. If you find that you have unused or overallocated space, you can release it so that the unused space can be used by other segments.
Within a segment, the high water mark indicates the amount of used space. You cannot release space below the high water mark (even if there is no data in the space you wish to deallocate). However, if the segment is completely empty, you can release space using the TRUNCATE DROP STORAGE statement.
ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
When you explicitly identify an amount of unused space to KEEP, this space is retained while the remaining unused space is deallocated. If the remaining number of extents becomes smaller than MINEXTENTS, the MINEXTENTS value changes to reflect the new number. If the initial extent becomes smaller, the INITIAL value changes to reflect the new size of the initial extent.
If you do not specify the KEEP clause, all unused space (everything above the high water mark) is deallocated, as long as the size of the initial extent and MINEXTENTS are preserved. Thus, even if the high water mark occurs within the MINEXTENTS boundary, MINEXTENTS remains and the initial extent size is not reduced.
See Also: For details on the syntax and options associated with deallocating unused space, see the Oracle7 Server SQL Reference.
You can verify that deallocated space is freed by looking at the DBA_FREE_SPACE view. For more information on this view, see the Oracle7 Server Reference.
For details about the DBMS_SPACE package, see ![[*]](jump.gif)
.
Example 1
Table dquon consists of three extents (see figure Figure 10 - 3). The first extent is 10K, the second is 20K, and the third is 30K. The high water mark is in the middle of the second extent, and there is 40K of unused space. The following statement deallocates all unused space, leaving table dquon with two remaining extents. The third extent disappears, and the second extent size is 10K.
ALTER TABLE dquon DEALLOCATE UNUSED;
Figure 10 - 3. Deallocating All Unused Space
If you deallocate all unused space from dquon and KEEP 10K (see Figure 10 - 4), the third extent is deallocated and the second extent remains in tact.
Figure 10 - 4. Deallocating Unused Space, KEEP 10K
If you deallocate all unused space from dquon and KEEP 20K, the third extent is cut to 10K, and the size of the second extent remains the same.
ALTER TABLE dquon DEALLOCATE UNUSED KEEP 20K;
Example 2
When you issue the ALTER TABLE dquon DEALLOCATE UNUSED statement, you completely deallocate the third extent, and the second extent is left with 10K. Note that the size of the next allocated extent defaults to the size of the last completely deallocated extent, which in this example, is 30K. However, if you can explicitly set the size of the next extent using the ALTER ... STORAGE [NEXT] statement.
Example 3
To preserve the MINEXTENTS number of extents, DEALLOCATE can retain extents that were originally allocated to an instance (added below the high water mark), while deallocating extents that were originally allocated to the segment.
For example, table dquon has a MINEXTENTS value of 2. Examples 1 and 2 still yield the same results. However, if the MINEXTENTS value is 3, then the ALTER TABLE dquon DEALLOCATE UNUSED statement has no effect, while the ALTER TABLE dquon DEALLOCATE UNUSED KEEP 10K statement removes the third extent and changes the value of MINEXTENTS to 2.