Oracle 10 g – Unable to free up space in tablespace

The free and open-source SchemaCrawler tool that I wrote will do what you need. SchemaCrawler outputs details of your schema (tables, views, procedures, and more) in a diff-able plain-text format (text, CSV, or XHTML). SchemaCrawler can also output data (including CLOBs and BLOBs) in the same plain-text formats. You can use a standard diff program to diff the current output with a reference version of the output.

https://www.SchemaCrawler.com

You will need to provide a JDBC driver for your database.

select tablespace_name, round(sum(bytes)/(1024*1024)) free_mb 
from dba_free_space group by tablespace_name;

But it isn't always that simple. While there may be, for example, 100M available and you may want to create an index that you estimate will be 80M, it may try to grab a new extent of 120M and fail.

Also, if you had a table of 50M and dropped it, your 100M available may be two chunks of 50M. Again, if the database tries to get a 100M extent, it can fail.

And if the tablespace has no free space but is set to autoextend (segment_space_management on dba_tablespaces) then it may show up as having insufficient space but would work. Or not, depending if the underlying file system has enough space.