Reorganization Activity for table for release unused space oracle

Reorganization Activity for table for release unused space oracle

Reorganization Activity for table for release unused space oracle.

1. Check the size of tables.

Select table_name, round((blocks*8)/1024,2)||'MB' "size" from user_tables where table_name = 'EMP';

2. Check the actual size of tables

select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from user_tables  where table_name = 'EMP';

3. Enable the row movement for the table which need to shrink.

   For this we must first allow ORACLE to change the ROWIDs of these rows by issuing 

    ALTER TABLE emp ENABLE ROW MOVEMENT;

   ROWIDs are normally assigned to a row for the life time of the row at insert time.

   After we have given Oracle the permission to change the ROWIDs

4. Now start Shrink Space

      We have three option for Shrink the table:

       -- Recover space and amend the high water mark (HWM).

           ALTER TABLE scott.emp SHRINK SPACE;

       -- Recover space, but don't amend the high water mark (HWM).

           ALTER TABLE scott.emp SHRINK SPACE COMPACT;

       -- Recover space for the object and all dependant objects.

           ALTER TABLE scott.emp SHRINK SPACE CASCADE;

This statement will proceed in two steps:

•  The first step makes the segment compact by moving rows further down to free blocks at the beginning of the segment.
• The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,but for a very short moment only.

Table shrinking advantage: 

• will adjust the high watermark
• can be done online
• will cause only rowlocks during the operation and just a very short full table lock at the end of the operation
• indexes will be maintained and remain usable
• can be made in one go
• can be made in two steps

Note :  this can be useful if you cannot get a full table lock during certain hours:
you only make the first step and adjust the high watermark later
when it is more convenient