Friday, November 23, 2012

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;  

No comments:

Post a Comment