Monitoring tablespace usage

 Here is the script to monitor space used by tablespaces of a database. This is especially useful when tablespaces are autoextend off. Note that we are doing left outer join with dba_free_space, this is useful when the tablespace is completely exhausted and does not have any extents showing in dba_free_space (I do not find any query on Internet that does left outer join to dba_free_space, so wrote this one).

select *

  from (select ts.TABLESPACE_NAME                                       Tablespace_Name,

               round( ( total_mb                   ) )                  Total_MB,

               round( ( total_mb - nvl(free_mb, 0) ) )                  Used_MB,

               round( ( nvl(free_mb, 0)            ) )                  Free_MB,

               round( ( total_mb - nvl(free_mb, 0) ) * 100 / total_mb ) Pecentage_Used

          from (select TABLESPACE_NAME, sum(BYTES) / (1024*1024) total_mb

                  from dba_data_files

                 group by TABLESPACE_NAME) ts,

               (select TABLESPACE_NAME, sum(BYTES) / (1024*1024) free_mb

                  from dba_free_space

                 group by TABLESPACE_NAME) fs

         where ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))

 where TABLESPACE_NAME like '%' -- Filter as needed

 order by Pecentage_Used desc;