Oracle SQL to Show Free Space In Tablespaces

Here is a nifty bit of SQL that will show you the used space and free space for all the tablespaces in an Oracle database.

 1. #This SQL can only be ran as SYSDBA or with a database user with "SELECT ANY DICTIONARY" privilege:
 2.  
 3. SELECT df.tablespace_name ,
 4. df.total_space_mb TOTAL_SPACE_MB,
 5. (df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
 6. fs.free_space_mb FREE_SPACE_MB,
 7. ROUND(100 * ((df.total_space_mb - fs.free_space_mb) / df.total_space_mb),2) PCT_USED
 8. FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
 9. ROUND(SUM(bytes) / 1048576,2) TOTAL_SPACE_MB
10. FROM dba_data_files
11. GROUP BY tablespace_name) df,
12. (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
13. ROUND(SUM(bytes) / 1048576,2) FREE_SPACE_MB
14. FROM dba_free_space
15. GROUP BY tablespace_name) fs
16. WHERE df.tablespace_name = fs.tablespace_name(+)
17. ORDER BY fs.tablespace_name;
Hide line numbers

1 comment:

Pallavi said...

Ok, where did you pinch this from? This is really neat