Take the 2-minute tour ×
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. It's 100% free, no registration required.

I'm using a Oracle 10g database. I have a big tablespace MYTBS made of a dozen data files (MYTBS_01, MYTBS_02, ...) of different sizes which have been added through the years.

Now i have removed very big tables from this tablespace and i would like to retrieve disk space on the server.

How can i be able to free up space/blocks from the tablespace ?

share|improve this question

migrated from stackoverflow.com Jan 23 '13 at 11:35

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

    
add comment

2 Answers 2

up vote 7 down vote accepted

You need to shrink the data files, which can be tricky.

However, possibly the easier method to describe and implement is to create a new tablespace of approximately the right size, use ALTER TABLE to move the table segments, and ALTER INDEX to rebuild the indexes in the new tablespace. When the old tablespace is empty of objects you can drop it.

Oracle Enterprise Manager can do this automatically for you. This is described in detail in this post: http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php#em_reorg

share|improve this answer
add comment

Agree with David. Few more options shared below...

  1. Option: 1 "alter table ... move + rebuild indexes"
  2. Option: 2 “Create table as select” (CTAS)
  3. Option: 3 "export / truncate / import" Option: 4 Use
  4. dbms_redefinition pkg

Note: Oracle does not free up blocks even after dropping columns as well. So, option 2 can work to re-claim existing unused space in 'drop column' scenario.

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.