Oracle Database monitor tablespace script

With this script you can monitor tablespaces sizes in oracle database. This script returns next columns:

Tablespace - tablespace name

Size MB - currently allocated space of tablespace in megabytes

Free MB - free size from allocated space in tablespace in megabytes

Max MB - size of maximum space until tablespace will extend in megabytes

Maximum free space in MB - free space in tablespace until it will be full, this is real value for monitring because it is showing real free space in tablespace in megabytes

Used MB - what space is used frm allocated space by segments in tablespace in megabytes

Used % - percentage of used space in tablespace, this is also real monitoring value

Status - oracle offers best practice for tablespace used percentage values 85% - caution, 90% - alarm, 97% - critical, so this script is depend on this values.

(
select df.tablespace_name "Tablespace",
       round(df.tablespace_size / 1024 / 1024, 2) "Size MB",
       round(nvl(fs.free_space, 0) / 1024 / 1024, 2) "Free MB",
       round(df.tablespace_maxsize / 1024 / 1024, 2) "Max MB",
       round((df.tablespace_maxsize - (df.tablespace_size - round(nvl(fs.free_space, 0), 2)))
       / 1024 / 1024,2) "Maximum free space in MB",
       round((df.tablespace_size / 1024 / 1024 - fs.free_space / 1024 / 1024),2) "Used MB",     
       to_char(round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
                     df.tablespace_maxsize * 100,
                     2),
               '990D99') || ' %' "Used %",
       case
         when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
                    df.tablespace_maxsize * 100,
                    2) > 95 then
          'CRITICAL - 95%'
         when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
                    df.tablespace_maxsize * 100,
                    2) > 90 then
          'ALARM - 90%'
         when round((df.tablespace_size - round(nvl(fs.free_space, 0), 2)) /
                    df.tablespace_maxsize * 100,
                    2) > 85 then
          'CAUTION - 85%'
         else
          'OK < 85%'
       end "Status"
  from (select 
               tablespace_name,
               sum(bytes) tablespace_size,
               sum(decode(maxbytes, 0, bytes, null, bytes, maxbytes)) tablespace_maxsize
          from dba_data_files
         group by tablespace_name) df,
       (select 
               tablespace_name, sum(bytes) free_space
          from dba_free_space
         group by tablespace_name) fs
 where df.tablespace_name = fs.tablespace_name(+)
 )
UNION
(
select 
       ts.tablespace_name "Tablespace",
       round(ts.tablespace_size / 1024 / 1024, 2) "Size MB",
       round(nvl(tfs.free_space, 0) / 1024 / 1024, 2) "Free MB",
       round(ts.tablespace_maxsize / 1024 / 1024, 2) "Max MB",
       round((ts.tablespace_maxsize - (ts.tablespace_size - tfs.free_space))
       / 1024 / 1024, 2) "Maximum free space in MB",
       round((ts.tablespace_size / 1024 / 1024 - tfs.free_space / 1024 / 1024),2)
        "Used MB",
       to_char(round((ts.tablespace_size - tfs.free_space) /
                     ts.tablespace_maxsize * 100,
                     2),
               '990D99') || ' %' "Used %",
       case
         when round((ts.tablespace_size - tfs.free_space) /
                    ts.tablespace_maxsize * 100,
                    2) > 95 then
          'CRITICAL - 95%'
         when round((ts.tablespace_size - tfs.free_space) /
                    ts.tablespace_maxsize * 100,
                    2) > 90 then
          'ALARM - 90%'
         when round((ts.tablespace_size - tfs.free_space) /
                    ts.tablespace_maxsize * 100,
                    2) > 85 then
          'CAUTION - 85%'
         else
          'OK < 85%'
       end "Status"
  from (select 
               tablespace_name,
               sum(bytes) tablespace_size,
               sum(decode(maxbytes, 0, bytes, null, bytes, maxbytes)) tablespace_maxsize
          from dba_temp_files
         group by tablespace_name) ts,
       (select
               tablespace_name, free_space from dba_temp_free_space) tfs
 where ts.tablespace_name = tfs.tablespace_name
    )
order by 7 desc, 1 asc;

note :
If it'll work slow on your database, main reason for this is your recyclebin, if there is too many objects then dba_free_space view will work slow, solution for this is to purge dba_recyclebin