Tablespace Space Used (%) ~ Am I really full?

For as many OEMs I’ve setup, the one metric that has always amazed me has been the  “Tablespace Space Used (%)” metric.  This metic is often misunderstood although it “should” be quite simple to understand.  What is so hard to understand about percentage (%) used?

In reviewing the documentation for OEM 11g and OEM 12c, the explanation for this metric has not changed much between releases.  The calculation that is performed to trigger this metic is really simple math:

Tablespace Space Used (%) = (TotalUsedSpace / MaximumSize) * 100

Once this metric has been triggered, most DBAs start scrambling to perform one of the following task:

  • Increase the size of the tablespace
  • Reorganizing the entire tablespace (fragmentation issues)
  • Relocate segements to another tablespace
  • Run Segment Advisor on the tablespace

What I have come to find out is, some times OEM will trigger this metric and the data files may not need any adjustments.  In order to get a clearer understanding of what caused this metric to trigger, we need to look at the “fulTbsp.pl” script.  This script is located in the $AGENT_HOME/sysman/admin/scripts directory.

In reviewing the “fulTbsp.pl” script, Oracle is not only looking at the current size of the data files and the maxsize of the datafile; they are looking at the file system space as well.  The reason for this is to ensure that the data files have enough space to expand if needed.

Now, here is where it can become misleading.  By setting the Tablespace Space Used (%) metric for critical to 95, we are thinking that the metric will trigger when the tablespace reaches 95% used, correct.  Before rushing to perform the tasks above, lets check and see what space is actually used in the tablespace.  In order to do this, Oracle provides us with a DBA view (DBA_TABLESPACE_USAGE_METRICS) to review the percentage of tablespace used.  Below I have provided a sample query for getting the usage of a tablespace:

select
           tablespace_name, 
           round(used_space/(1024*1024),2), 
           round(tablespace_size/(1024*1024),2), 
           round(used_percent, 2) 
from
           dba_tablespace_usage_metrics
where
           round(used_percent,2) > 90;

Often, I have found that when an alert is triggered for the Tablespace Space Used (%) metric, the data files are less than 90% full.  This is due to the alert being triggered because OEM makes the determination that there is not enough space on the file system to expand the data file if needed.  If you keep this in mind, you’ll be able to keep a firm grasp on what is going on from the OEM and your tablespaces.

Enjoy!

 

 

Please follow and like:
Comments
  • Hi – good article, but this view measures in blocks, not bytes so you should join it to the dba_tablespaces view and use the block_size field. Your MB used calc then becomes used_space*block_size/1024/1024. A good combined query is
    column used_megs format 999,999
    column total_size format 999,999
    column used_percent format 99
    break on contents
    select t1.contents,
    t0.tablespace_name,
    t0.used_space*t1.block_size/1024/1024 used_megs,
    t0.tablespace_size*t1.block_size/1024/1024 total_size,
    t0.used_percent
    from dba_tablespace_usage_metrics t0,
    dba_tablespaces t1
    where t0.tablespace_name = t1.tablespace_name
    order by 1, 2;

    • Sir,
      After digging around for additional information, you are correct the unit of measurement on the dba_tablespace_usage_metric view is done in blocks. Thanks for providing another good example of code. I have added rounding function to the SQL to make it a bit clearer.

      select t1.contents,
      t0.tablespace_name,
      round(t0.used_space*t1.block_size/1024/1024,2) used_megs,
      round(t0.tablespace_size*t1.block_size/1024/1024,2) total_size,
      round(t0.used_percent, 2) used_percent
      from dba_tablespace_usage_metrics t0,
      dba_tablespaces t1
      where t0.tablespace_name = t1.tablespace_name
      order by 1, 2;

      Enjoy!

Comments are closed.

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.