Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted
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.

share|improve this answer
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 '09 at 23:10
    
Thanks, awesome answer. –  jeffspost Sep 24 '09 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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