How to DeallocateUnusedSpace

How to DeallocateUnusedSpace

How to Deallocate Unused Space from a Table, Index or Cluster. (Doc ID 115586.1) To BottomTo Bottom 

Checked for relevance on 21-May-2010

Checked for relevance on 31-Oct-2011

Checked for relevance on 26-AUG-2014

NOTE

---

For versions 10G+, and using ASSM:

Segment shrink is an alternative way to release space.

Refer to Note 242090.1 SEGMENT SHRINK and details.

PURPOSE

---

This document explains the purpose and use of the DEALLOCATE UNUSED KEEP

clause of the ALTER TABLE, ALTER INDEX, and ALTER CLUSTER commands.

SCOPE & APPLICATION

---

The article will be useful for Oracle Dba's, developers and Support Analysts.

How to Deallocate Unused Space from a Table, Index or Cluster

---

Times when the DBA finds that he has overallocated the space required for a table, index, cluster or the table, index, or cluster has shrunk significantly, then the space can be freed up if certain restrictions can

be met.

The unused space can be reclaimed using the DEALLOCATE clause in the ALTER TABLE, ALTER INDEX, and ALTER CLUSTER commands.

The freed space is then available for re-use by other objects in the tablespace. In addition, the user who releases the space is credited for the freed space in his available space quota.

The syntax is:

DEALLOCATE UNUSED;

and 

DEALLOCATE UNUSED KEEP nn; in bytes

DEALLOCATE UNUSED KEEP nnK; in kilobytes

DEALLOCATE UNUSED KEEP nnM; in megabytes

The KEEP parameter specifies the number of bytes above the high water mark the object needs to retain, even if there are no rows in that space. 

Examples:

ALTER TABLE ExampleTable DEALLOCATE UNUSED;

ALTER INDEX ExampleIndex DEALLOCATE UNUSED KEEP 100K;

ALTER CLUSTER ExampleCluster DEALLOCATE UNUSED KEEP 10M;

Restrictions:

1. You can reclaim only the space above the high water mark in a segment.

2. The high water mark represents the highest buffer that has been formated to receive data. The space above this buffer in which data has never been stored is the only space that can be released.

The only way the high water mark can be moved downward is to truncate the segment or drop and recreate the segment. This of course removes all the data from the segment, but the high water mark is reset.

The exact amount of space that is deallocated depends on the values of the INITIAL, MINEXTENTS, and NEXT parameters.

If you do not use the KEEP option then:

If an extent is entirely above the high water mark the whole extent is deallocated. 

If the extent is partly above the high water mark, the part above is deallocated, and the part below becomes the extent.

If the high water mark is above the size of INITIAL and MINEXTENTS, then all unused space above the high water mark is deallocated.

If the high water mark is less then the size of INITIAL or MINEXTENTS, then all unused space above MINEXTENTS is deallocated.

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

If you do use the KEEP option then:

If an extent is above the high water mark, and above the KEEP area then the entire extent is deallocated.

If the extent is partially above the high water mark and KEEP area, then the space above is deallocated, and the space below becomes the extent. 

If the remaining number of extents if less than MINEXTENTS, then MINEXTENTS is set to the new number of extents.

If the initial extent in the object becomes smaller then the INITIAL parameter, then the parameter INITIAL is set to the size of the inital extent.

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

EXAMPLES

---

In this example assume that you are a user SCOTT, and you have a table EMP, and you want to deallocate the spare space.

You can first determine the space available to be deallocated by using the DBMS_SPACE package as follows:

set serveroutput on

declare

TOTAL_BLOCKS number;

TOTAL_BYTES number;

UNUSED_BLOCKS number;

UNUSED_BYTES number;

LAST_USED_EXTENT_FILE_ID number;

LAST_USED_EXTENT_BLOCK_ID number;

LAST_USED_BLOCK number;

begin

dbms_space.unused_space('SCOTT','EMP','TABLE',

TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,

LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,

LAST_USED_BLOCK);

dbms_output.put_line('OBJECT_NAME = EMP');

dbms_output.put_line('-----------------------------------');

dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);

dbms_output.put_line('TOTAL_BYTES = '||TOTAL_BYTES);

dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);

dbms_output.put_line('UNUSED BYTES = '||UNUSED_BYTES);

dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||LAST_USED_EXTENT_FILE_ID);

dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);

dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK);

end;

/

The output is:

SVRMGR> @space1

Server Output ON

Statement processed.

OBJECT_NAME = EMP

---

TOTAL_BLOCKS = 5

TOTAL_BYTES = 10240

UNUSED_BLOCKS = 3

UNUSED BYTES = 6144

LAST_USED_EXTENT_FILE_ID = 1

LAST_USED_EXTENT_BLOCK_ID = 17625

LAST_USED_BLOCK = 2

SVRMGR> 

Or you can determine the number of empty blocks and unused bytes by using (in this example db_block_size has been assumed to be 4096):

SQL> analyze table scott.emp compute statistics;

Table analyzed.

SQL> select empty_blocks, empty_blocks*4096 unused_bytes

2 from dba_tables 

3 where table_name='EMP' and owner='SCOTT';

EMPTY_BLOCKS 

---

2

The high water mark of the table in bytes is the difference between the TOTAL_BYTES value and the UNUSED_BYTES value, which in this example is 4096.

So this means that the first 4096 bytes of the table have data stored in them, and the next 6144 bytes are available to be deallocated.

This can be accomplished by using the command:

ALTER TABLE scott.emp DEALLOCATE UNUSED;