This discussion is archived
1 Reply Latest reply: Jul 27, 2012 3:45 AM by Kev82Fr RSS

Shrink Oracle Table after Deletion

user438428 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points