1 Reply Latest reply: Jul 27, 2012 5:45 AM by Kev82Fr RSS

    Shrink Oracle Table after Deletion

    user5421381
      A few database tables are very big. An Oracle table still holds the disk space occupied by deleted records according to http://stackoverflow.com/questions/6540546/oracle-10g-table-size-after-deletion-of-multiple-rows. Re: shrink table after delete teaches the following commands to release the idle space from the table.
      ALTER TABLE TABLE1 DEALLOCATE UNUSED;

      ALTER TABLE TABLE1 ENABLE ROW MOVEMENT;
      ALTER TABLE TABLE1 SHRINK SPACE;

      ALTER TABLE TABLE1 MOVE;

      1. Are the commands feasible?
      2. Are they safe?
      3. What will be the impacts of running the commands?
      4. Is there any other workable safe approach shrinking a table?
        • 1. Re: Shrink Oracle Table after Deletion
          Kev82Fr
          Hi,

          I advise using shrink table operation.

          The tablespace which belong to your table must be in ASSM (Automatic segment space management) to use shrink.

          Shrink is safe but you need to run two commands :

          1)ALTER TABLE TABLE1 SHRINK SPACE COMPACT; (This is long operation which moves data, but can be done online)

          2)ALTER TABLE TABLE1 SHRINK SPACE; (this is quick if you run SHRINK SPACE COMPACT before, it only shift the High Water Mark. Be carreful if you don't run SHRINK SPACE COMPACT before your table will be locked for a long time)

          Another point, is that execution plans are calculed using the HWM so SHRINK the table (The 2nd command) will invalidate all the cursors in shared pool where the table is in. So execution plan need to be recalculated (often not a problem).

          Regards,