Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from Jan 22 '12 at 15:54

This question came from our site for professional and enthusiast programmers.

try moving the table to another tablespace and back. – Benoit Jan 18 '12 at 21:24
@benoit : I dont have an another tablespace to move the contents of a table.I need to free the space using the alter command. I tried to understand the alter index coalesce command but couldnt figure out it. – Raghavendar Jan 18 '12 at 21:39
@Raghavendar - Can you define "did not work"? Did you get an error? If so, what error? If you did not get an error, what exactly did not work? Are you looking at tablespace usage or data file usage statistics, for example? – Justin Cave Jan 18 '12 at 21:40
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;
share|improve this answer
I tried the way you suggested me but I am getting an error similar to below: ORA-01652: unable to extend temp segment by 8192 in tablespace name When I try to add space the tablespace, I am getting an error. Please advise on adding space to the tablespace – user6145 Jan 25 '12 at 21:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.