Datafiles space usage

One of my most often used queries is the one that returns a list of tablespaces and datafiles with their size, amount of used space and free space. Although, this information is returned nicely by most of DBA tools like OEM, Toad, SQL Developer, somehow I have often got this information by querying the database manually. Queries give much flexibility including customizing the output like grouping by tablespace, sorting by space left, converting units from MBs to GBs, filtering other users' tablespaces out and much more. Also these queries can be a part of a more complex script ... but it is out of topic (if you are interested, see the article about scripts vs tools).

Three views are helpful for this data:

• dba_data_files - contains a list of all data files with information about their sizes and tablespaces they belong to
• dba_extents - list of extents, I use it for amount of used space
• dba_free_space - not used space in datafiles

Combined together can be used to get a list of all datafiles with amount of disk space each of them consumes, amount of space already allocated and used, amount of free space still available for usage without extending datafiles, percentage of free space. Additionally, last column indicates whether the file has autoextent option enabled or not.

select df.tablespace_name, df.file_name, round(df.bytes/1024/1024) totalSizeMB, nvl(round(usedBytes/1024/1024), 0) usedMB, nvl(round(freeBytes/1024/1024), 0) freeMB,
    nvl(round(freeBytes/df.bytes * 100), 0) freePerc, df.autoextensible
from dba_data_files df
    left join (
        select file_id, sum(bytes) usedBytes
        from dba_extents
        group by file_id
    ) ext on df.file_id = ext.file_id
    left join (
        select file_id, sum(bytes) freeBytes
        from dba_free_space
        group by file_id
    ) free on df.file_id = free.file_id
order by df.tablespace_name, df.file_name