The Oracle (tm) Users' Co-Operative FAQ

How I determine how much free space a tablespace has left?


Author's name: Norman Dunbar;

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 24/08/2001

Oracle version(s): 7.3.4 to 8.1.7

How I determine how much free space a tablespace has left?

Back to index of questions


As the SYSTEM user

The following query will tell you how much free space there is in all your tablespaces and should be run as a dba user such as sys or system. There is a caveat, which will be explained later.

    select tablespace_name, to_char(sum(bytes)/1048576, '99,999,990.99') as MB_FREE, 
    count(*) as FRAGMENTS, to_char(max(bytes)/1048576, '99,999,990.99') as BIGGEST_BIT
    from dba_free_space
    group by tablespace_name;

which gives something like the following results :

    TABLESPACE_NAME                MB_FREE         FRAGMENTS BIGGEST_BIT
    ------------------------------ -------------- ---------- --------------
    CONFIG                                 118.77         22          98.02
    MY_DATA                                198.91          1         198.91
    MY_INDEX                                65.42          4          64.05
    RBS                                      9.99          1           9.99
    SYSTEM                                 148.40          1         148.40
    TEMP                                   419.99          3         339.99
    TOOLS                                    7.10          3           6.55

From this we can see that the CONFIG tablespace is a wee bit fragmented (that is free space fragments) and that the rest are ok. With 118 meg free and the biggest bit being 98, this implies that there are 21 free 'bits' of the CONFIG tablespace making up 20 Meg, so there are a lot of tiny bits of free space and this might lead to a problem allocating an initial extent when we have used up the big chunk at some point in the future.

OK the caveat, in the above script's results, any tablespace which has exactly zero bytes free will not have any entries in the DBA_FREE_SPACE table so will not appear in the results. This is fine if you know all your tablespace names in each of your databases, but if you are like me and have slightly failing memory facilities, then you'll need the following script instead :

    select ts.tablespace_name, to_char(sum(nvl(fs.bytes,0))/1048576, '99,999,990.99') as MB_FREE, 
    count(*) as FRAGMENTS, to_char(max(nvl(fs.bytes,0))/1048576, '99,999,990.99') as BIGGEST_BIT
    from dba_free_space fs, dba_tablespaces ts
    where fs.tablespace_name(+) = ts.tablespace_name
    group by ts.tablespace_name;

This joins the list of all tablespaces in DBA_TABLESPACES to the list of tablespaces which have free space in them but also includes an entry for any tablespace which has no entries in DBA_FREE_SPACE because we have used an outer join between the two tables. This gives us the following results :

    TABLESPACE_NAME                MB_FREE         FRAGMENTS BIGGEST_BIT
    ------------------------------ -------------- ---------- --------------
    CONFIG                                 118.77         22          98.02
    EMPTY                                    0.00          1           0.00   
    MY_DATA                                198.91          1         198.91
    MY_INDEX                                65.42          4          64.05
    RBS                                      9.99          1           9.99
    SYSTEM                                 148.40          1         148.40
    TEMP                                   419.99          3         339.99
    TOOLS                                    7.10          3           6.55

And now we can plainly see all tablespaces, whether they have free space or not, are listed.

As a non-SYSTEM user

An ordinary, non dba, user can also check to see how much space they have left in their own tablespaces or tablespaces which they have been granted quota on, simply by substituting USER_FREE_SPACE and USER_TABLEPSACES in the above script, as follows :

    select ts.tablespace_name, to_char(sum(nvl(fs.bytes,0))/1048576, '99,999,990.99') as MB_FREE, 
    count(*) as FRAGMENTS, to_char(max(nvl(fs.bytes,0))/1048576, '99,999,990.99') as BIGGEST_BIT
    from user_free_space fs, user_tablespaces ts
    where fs.tablespace_name(+) = ts.tablespace_name
    group by ts.tablespace_name;

which gives something like the following results :

    TABLESPACE_NAME                MB_FREE         FRAGMENTS BIGGEST_BIT
    ------------------------------ -------------- ---------- --------------
    EMPTY                                    0.00          1           0.00   
    MY_DATA                                198.91          1         198.91
    MY_INDEX                                65.42          4          64.05

Further reading:

Interestingly enough, the best information I have come across is in my Oracle DBA training manual for Oracle & Database Administration, chapter 7. The latest manuals only have a fleeting reference to DBA_FREE_SPACE in the Administrator's Guide page 17-36 example 6 but this doesn't mention that full up tablespaces won't appear in the results.


Back to top

Back to index of questions