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.

  • 2
    Short answer-- no. You could run alter database ... to resize the data files to make them smaller, or you could transfer the tables to a new tablespace and then drop the old.
    – gsiems
    Commented Nov 13, 2019 at 23:17

1 Answer 1


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


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.

  'free space' owner, null OBJECT, null segment_type, file_id, block_id, blocks
FROM dba_free_space
WHERE tablespace_name = UPPER ('&Tblspc')
  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>;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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