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
Would you like to learn db performance? Enroll to my course on Udemy.
Promo code: PERF_OPT_0522