How to free space after deleting the millions of rows from a table

Question

I performed a delete operation on my table . It deleted around 10 millions of rows from it

How do I free the space associated with the deleted data.

I used the query:

alter table tablename shrink space;

But it did not work for me. Do I need to concern about the index's associated with the table?

If that is the case, how do I need to do it.

Answer 1

alter table TABLENAME move;

followed by rebuilding the indexes on that table should do what you want.

After 'moving' the table around, the indexes for the rows will become unusable (as you can verify with something like):

select index_name, status from all_indexes 
 where table_name = 'TABLENAME' and
       owner      = 'SOMEUSER';

So, you would rebuild such an unusable index with a

alter index TABLENAME_IX_01 rebuild;

Answer 2

Might be helpful:

• Reclaiming Wasted Space
• Reclaiming Oracle disk space