Oracle free space after deleting data from tables

Question

I recently deleted a big chunk of data from some tables in the database. Now I wish to free up the space that was being held by that data. After that I would like to rebuild indexes. What is the best way to free up the space?

Answer 1

What do you mean by "free up the space"? When you deleted the data, space was freed up in the blocks. That space is now available for subsequent inserts (or updates) in the table you deleted data from. That's normally sufficient because the table will normally grow again in the future.

You could shrink the table if you want to decrease the size of the table segment and make the space available to other segments in the same tablespace

ALTER TABLE table_name ENABLE ROW MOVEMENT;

ALTER TABLE table_name SHRINK SPACE CASCADE;

There is generally no need at that point to rebuild the indexes.