I need to add some indexes to a moderately large table and I'd like to know in advance if the tablespace has room to hold the index. (The extents are suppose to automatically increas in size, but the DBA's don't always have that set up correctly and I don't want to be the one that crashes the system.) What is the SQL I need to run to see how much space is available?
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.