Oracle 11gR2 can I delete datafiles to reduce tablespace?

Question

I have the following doubt, maybe a little silly, if I have a tablespace for example TBS_MYDATA of 155 GB, this tablespace is composed of 5 datafiles of 31GB each, in turn, each of these datafiles have between 3% and 4% usage ... my question is: can I delete some of these datafiles to reduce the size of the tablespace? The information that these datafiles contain would be automatically relocated in the remaining ones? Thank you very much in advance.

Answer 1

You can use DROP DATAFILE from ALTER TABLESPACE to drop a datafile which is empty (has no extents allocated from it).

Example:

ALTER TABLESPACE <tablespace_name> DROP DATAFILE '/path/datafile_name';

However if the datafile is not empty, you need to run something like a tablespace map to see which object extents are allocated from it. You can use this script, but there are other searchable scripts which give pretty outputs.

SELECT   
  'free space' owner, null OBJECT, null segment_type, file_id, block_id, blocks
FROM dba_free_space
WHERE tablespace_name = UPPER ('&Tblspc')
UNION
SELECT   
  owner, segment_name, segment_type, file_id, block_id, blocks
FROM dba_extents
WHERE tablespace_name = UPPER ('&Tblspc')
ORDER BY 4, 5;

Once you find the objects in the datafile you want to drop, you can reorganize them into the same tablespace (which might put them into free space in other datafiles) or other tablespaces.

Here are some example commands for moving tables and indexes.

ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name>;
ALTER INDEX <index_name> REBUILD TABLESPACE <tablespace_name>;

With those examples don't forget to add your storage and statistics clauses as needed.

You could also try resizing datafiles but this will only work up to an allocated extent. This means that if there is an extent half way through the datafile, you would only be able to resize it to 50%. You can use the tablespace map to check these also, or do it by trial and error.

ALTER DATABASE DATAFILE '/path/datafile_name' RESIZE <new_size>;