Wednesday, November 28, 2012

Reclaim Space in Tablespace using Shrink Command

SQL> ALTER TABLE TABLE_NAME enable ROW movement;

SQL> ALTER TABLE TABLE_NAME shrink SPACE;

SQL> ALTER TABLE TABLE_NAME disable ROW movement;

Benefits regarding to Oracle Documentation :

  • Full table scans will take less time (a table scan will always scan up to the HWM even if space is not used)
  • Better index access takes place because of a smaller B-Tree
  • Space is freed up for other database objects
  • Space below the HWM is released and the HWM is moved down

 Thank you
Osama Mustafa

2 comments:

  1. does this operation invalidate indexes??? because we are moving rows down across the oracle block..

    ReplyDelete