How to retrieve disk spaces by removing datafiles from tablespaces in Oracle 10g?

Question

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 do to be able to remove datafiles from the tablespace ?

Answer 1

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.