Releasing Oracle Unused Space: Quick & Efficient Solution!!

Author: Michael Stephen

Page: 1 2

Oracle provides a number of options to help you reclaim unused space for database objects. In this article we will discuss a number of options that will help you release unused space in your Oracle databases.

DELETE Command:

Many times we delete records in a table due to space problem. However it should be noted that delete do not reclaim free space from deleted rows. Instead Oracle keeps this space for future insertions.

ALTER TABLE --- DEALLOCATE UNUSED Command:

Oracle DEALLOCATE UNUSED can be used to explicitly de-allocate unused space at the end of the table so that the space can be used by other segments in the tablespace. If your high water mark is above MINEXTENTS then this command will free all unused space for reuse in table. DEALLOCATE UNUSED would be the fastest. It will only release unused blocks above the high level mark. Use below command to de-allocate unused extents.

ALTER TABLE TABLE_NAME DEALLOCATE UNUSED KEEP INTEGER;
ALTER INDEX INDEX_NAME DEALLOCATE UNUSED KEEP INTEGER;
ALTER CLUSTER CLUSTER_NAME DEALLOCATE UNUSED KEEP INTEGER;

You can run the DBMS_SPACE package's UNUSED_SPACE procedure prior to deallocation. This will give you information about the position of the high water mark and the amount of unused space in a segment. You can also use the optional KEEP clause to specify the amount of space retained in the segment of table, index or cluster. You can examine the DBA_FREE_SPACE view to verify that the deallocated space is freed.

ALTER TABLE --- SHRINK SPACE Command:

Oracle SHRINT SPACE cab be used to manually shrink space in a table.

ALTER TABLE TABLE_NAME SHRINK SPACE;

Oracle SHRINT SPACE command can be used only for segments in tablespaces with automatic segment management. You should disable any ROWID based triggers before using Shrink. You will also need to enable row movement for heap-organized segments.

ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;

You can not shrink the tables that are part of a cluster, tables with LONG columns, tables with function-based indexes, certain types of materialized views and certain types of IOTs. Oracle 10.2 allows you to shrink LOB Segments, function based indexes and IOT overflow segments.

Usually attempting shrink in one longer step takes longer time depending upon pre delete table size. However you can use the SHRINK SPACE COMPACT clause to accomplish this task in two shorter steps. This command requires less block accesses and hence full table scans run faster. 

ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT;

COMPACT makes Oracle database only de-fragment the segment space and compact the table rows for subsequent release. Neither the HWM is readjusted nor is the space released immediately. You can complete the operation by running another ALTER TABLE ... SHRINK SPACE command later.

Using Oracle SHRINK SPACE COMPACT CASCADE makes Oracle perform the same operations on all dependent objects of table including secondary indexes on index-organized tables.

ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT CASCADE;

Table Recreation:

You can solve space problem by using CREATE TABLE AS SELECT ..... for re-creating table with a different . After that you will need to check constraints and dependencies etc and then put database in suspension for few moments. Now you will have to rename old table and rename new table to the name of old table. It is advisable to plan a maintenance window to attempt this option. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Remote DBA and Oracle Remote DBA Support Services
   Oracle DBMS: Fundamental Security Precautions
   Great Tips on Optimizing Oracle Network Configuration!!
   Zend Core To Appear - Oracle and Zend’s Partnership