Oracle Tables that undergo significant data manipulation (DML) activity, such as UPDATE and DELETE operations, can become fragmented and contain free space inside the table segments. Such fragmentation reduces the efficiency of scanning operations like full table scans of index scans. Before oracle 10g one should have used "MOVE TABLE" in order to solve this problem. Oracle 10g introduced us with new online feature, shrink table. 

Alter table shrink space

 Using the "alter table xxx shrink space compact" command will re-pack the rows, move down the HWM, and releases unused extents.  With standard Oracle tables, you can reclaim space with the "alter table shrink space" command:

 -- Enable row movement.
ALTER TABLE test ENABLE ROW MOVEMENT;

 -- Recover space and amend the high water mark (HWM).
ALTER TABLE test SHRINK SPACE;

 -- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE test SHRINK SPACE COMPACT;

 -- Recover space for the object and all dependant objects.
ALTER TABLE test SHRINK SPACE CASCADE;                      

Finding Candidates for Shrinking

Before performing an online shrink, you may want to identify the segments that can be most fully compressed. Simply use the built-in function verify_shrink_candidate in the package dbms_space. Execute this PL/SQL code to test if the segment can be shrunk to 2000000 bytes.

 begin
   if (dbms_space.verify_shrink_candidate
         ('MJBDB','ASSET','TABLE',2000000)
   ) then
       :x := 'T';
   else
       :x := 'F';
   end if;
end;
/

PL/SQL procedure successfully completed.

CREATE OR REPLACE PACKAGE dbceo.shrink_space AS

   PROCEDURE shrink_table(tab_owner VARCHAR2, tab_name VARCHAR2);

END;
/

show errors


CREATE OR REPLACE PACKAGE BODY dbceo.shrink_space AS


   PROCEDURE shrink_table(tab_owner VARCHAR2, tab_name VARCHAR2) IS

      ct NUMBER;

      BEGIN
      SELECT count(*) INTO ct FROM shrink_candidate WHERE towner = tab_owner AND tname = tab_name;
      IF ct=1 THEN
         EXECUTE IMMEDIATE 'alter table '||tab_owner||'.'||tab_name||' enable row movement';
         dbms_output.put_line('Enabled row movement for '||tab_owner||'.'||tab_name);
         EXECUTE IMMEDIATE 'alter table '||tab_owner||'.'||tab_name||' shrink space';
         dbms_output.put_line('Shrank table '||tab_owner||'.'||tab_name);
         EXECUTE IMMEDIATE 'alter table '||tab_owner||'.'||tab_name||' disable row movement';
         dbms_output.put_line('Disabled row movement for '||tab_owner||'.'||tab_name);
      ELSE
        DBMS_OUTPUT.PUT_LINE(tab_owner||'.'||tab_name||' is not currently enabled for shrinking. Please contact a DBA to set this up.');
      END IF;

      EXCEPTION
          WHEN OTHERS THEN
             dbms_output.put_line('SHRINK_TABLE '||tab_owner||'.'||tab_name||' SQLCODE: '||SQLCODE||' SQLERRM: '||SQLERRM);
      END shrink_table;

END shrink_space;
/

show errors