How to Reclaim Space from DBA_FREE_SPACE and assign back to OS

Dear Friends,

Today I will like to explain how to reclaim space from DBA_FREE_SPACE and allocate the same to Operating system.

1. Query from dba_free_space to verify the available free space.

select tablespace_name "Tablespace Name" ,sum(bytes/1024/1024/1024) "Free Space in GB"  from dba_free_space group by tablespace_name order by 2 desc;

2. Check the objects available in present tablespace.

3. Create a New Tablespace (XYZ_NEW) and add sufficient datafiles.

CREATE TABLESPACE <Tablaspace_name> DATAFILE '<Datafile Name>' SIZE 4000M  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Note :

a. Object migration from one tablespace to another tablespace will use the Temporary Tablespace. Usages of Temp tablespace is approx the same size of Objects (Table/Index).

b. Object migration will create excessive archive log files. Which may be another issue to manage space. To avoid this we can use nologging option in command.

c. We can use parallel command with alter index/table.

4. Migrate objects in new tablespace (Sample query depend on the objects)
select 'ALTER INDEX ' || owner ||'.' || segment_name || ' REBUILD TABLESPACE XYZ_NEW parallel 4 nologging; ' from dba_segments where tablespace_name='XYZ' and segment_type='INDEX'; select 'ALTER INDEX ' || owner ||'.' || segment_name || ' REBUILD PARTITION ' || partition_name || ' TABLESPACE XYZ_NEW; ' from dba_segments where tablespace_name='XYZ' and segment_type='INDEX PARTITION'; select 'alter table ' || owner|| '.' ||segment_name || '  move tablespace XYZ_NEW ;' from dba_segments where tablespace_name='XYZ' and segment_type='TABLE'; select 'alter table ' || owner|| '.' ||segment_name || '  move partition ' || partition_name ||' tablespace XYZ_NEW ;' from dba_segments where tablespace_name='XYZ' and segment_type='TABLE PARTITION';
5. Drop old tablespace
DROP TABLESPACE XYZ INCLUDING CONTENTS AND DATAFILES;
6. Rename tablespaces
ALTER TABLESPACE XYZ_NEW RENAME TO XYZ;
7. Compile invald objects sqlplus > @$ORACLE_HOME/rdbms/admin/utlrp.sql
8. Check if any invalid index and rebuild index if any unusable.
select distinct status from all_indexes;
alter index <index_name> rebuild online; Activity is completed and again we can verify the free space from select tablespace_name "Tablespace Name" ,sum(bytes/1024/1024/1024) "Free Space in GB"  from dba_free_space group by tablespace_name order by 2 desc;