Releasing Oracle Unused Space

Releasing Oracle Unused Space: Quick & Efficient Solution!!
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.
Table Reorganization:
Sometimes specially in ETL projects a lot of data loading, reloading and deletion is done due to which table size grew considerably even if it has less data. In such scenarios you can re-organize table and released unused table space.
First of all you will have to load data with direct path method. The direct-path INSERT appends data to the end of the table. This avoids the usage of existing space currently allocated to the table.
INSERT /*+ APPEND */
Now if you load data into table and use DELETE command then delete operation will not release the allocated space. Therefore the space will not be used by any direct load operation until the space is released.
Now you can reorganize the table in the same tablespace by using below command. This will allocate new space needed for the amount of data table has. The previously allocated space that doesn't have any more data is released. All the physical attributes of the table remain the same. You can also use the ONLINE option. The MOVE ONLINE option will take longer but it will release ALL unused blocks in the table.
ALTER TABLE TABLE_NAME MOVE; ALTER TABLE TABLE_NAME MOVE ONLINE;
Contracting Oracle Tablespaces:
Many times it happens that we end up with a 20GB datafile even if it has only 20MB of data in it. Oracle tablespaces supports a great feature to auto-extend but unfortunately we can not auto-contract it. Below SQL*Plus script can be used to identify the shrinkable datafiles. It will display each tablespace, its space and how much of it is being used. You can mark those tablespace as shrinkable that have high percentage free + high byte count.
SET LINESIZE 150COLUMN MYFILE FORMAT A45 COLUMN FREESPACE FORMAT 99.00
CREATE VIEW MY_FREESPACE AS (
SELECT SUM(BYTES) FREE_BYTES, TABLESPACE_NAME FROM DBA_MY_FREESPACE GROUP BY TABLESPACE_NAME
);
SELECT D.TABLESPACE_NAME, D.BYTES OVERALL_SIZE, F.FREE_BYTES/D.BYTES*100 FREESPACE, D.MYFILE MY_FREESPACE F, DBA_DATA_FILES DWHERE F.TABLESPACE_NAME = D.TABLESPACE_NAME;
DROP VIEW MY_FREESPACE;
Once you have identified the path to the shrinkable datafile then what you just need to do is to run ALTER DATABASE command to resize it. If you try to resize a tablespace to a size smaller than its contents then you will get an error.
ALTER DATABASE DATAFILE ‘C:\ORACLE\ORADATA\DEV2\MY_DATAFILE.ORA' RESIZE 600M;
Oracle Automatic Segment Advisor:
Oracle Automatic Segment Advisor is an automated maintenance task that is configured to run during all maintenance windows. Automatic Segment Advisor is implemented by the AUTO_SPACE_ADVISOR_JOB job which executes the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC procedure at predefined points in time. This job output contains the space problems found and the recommendations.
Oracle Automatic Segment Advisor examines database statistics and samples segment data. It then selects the tablespaces that have exceeded a critical or warning space threshold, the segments with most activity and the segments with highest growth rate for analysis.
Oracle DBA_AUTO_SEGADV_SUMMARY view can be used to display the information specific to Automatic Segment Advisor. Each row of this view summarizes one Automatic Segment Advisor run.