1

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?

1 Answer 1

3
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.

1
  • 1
    +1. And if the tablespace is a smallfile tablespace and the datafile maxes out in size even if autoextend is on it fails, as I ran into yesterday....
    – DCookie
    Sep 23, 2009 at 23:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .