RE: How does EM12c calculate tablespace freespace?

From: Deas, Scott <>
Date: Tue, 2 Jun 2015 15:26:52 +0000
Message-ID: <>

I should clarify, the issue has nothing to do with OEM, it's completely within the database, OEM just relies on this view that is incorrect.


From: Deas, Scott
Sent: Tuesday, June 02, 2015 11:26 AM
Subject: RE: How does EM12c calculate tablespace freespace?

We have environments through that have had wrong results from dba_tablespace_usage_metrics. One-off patch 18723434 was applied to correct the issue in our environments.

I'd open do a search on Metalink or open a ticket to find the appropriate solution, but it has been a known issue for a long time.


From:<> [] On Behalf Of Mladen Gogala Sent: Tuesday, June 02, 2015 11:13 AM
To:<> Subject: Re: How does EM12c calculate tablespace freespace?

On 06/02/2015 10:04 AM, Rich Jesse wrote:

Hey all,

EM popped a "Tablespace MYTBS is 97 percent full" incident last

week, but I haven't been able to determine why or how it thinks the

tablespace is at 97% capacity.

The target is on AIX. There are no rows in DBA_OUTSTANDING_ALERTS

and no tablespace alerts in DBA_ALERT_HISTORY in the target, so I'm guessing

this is an EM12c calculation rather than a DB one.

Working from the storage calc listed on MOS 1590051.1, I'm using this query

to show "percent full":

SELECT   SUM(s.used_gb)/SUM(f.total_gb)*100

FROM dba_tablespaces t,


  SELECT tablespace_name,

         SUM(NVL(bytes,0))/(1024*1024*1024) total_gb

  FROM dba_data_files

  WHERE TABLESPACE_NAME = 'MYTBS'   GROUP BY tablespace_name) f,


  SELECT tablespace_name,

         SUM(NVL(bytes,0))/(1024*1024*1024) used_gb

  FROM dba_segments

  WHERE TABLESPACE_NAME = 'MYTBS'   GROUP BY tablespace_name) s

WHERE t.tablespace_name = f.tablespace_name (+)

  AND t.tablespace_name = s.tablespace_name (+);

It returns a value of about 83.77, which is corroborated by Toad's Schema


There's much more data for this, but in the interest of brevity, I'll start

with this.

Thoughts anyone?



OEM queries dba_tablespace_usage_metrics. This is the description:
SQL> desc dba_tablespace_usage_metrics
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                    VARCHAR2(30)
 USED_SPACE                         NUMBER
 TABLESPACE_SIZE                    NUMBER
 USED_PERCENT                       NUMBER

If your version is older than 11.2, there may be a slight problem with it:
 This note gives a brief overview of bug 6759910.
 The content was last updated on: 06-NOV-2009
 Click here<javascript:getdoc('NOTE:245840.1')> for details of each of the sections below.
Product (Component)

Oracle Server (Rdbms)

Range of versions believed to be affected

Versions < 11.2

Versions confirmed as being affected

Platforms affected Generic (all / most platforms affected) Note that this fix has been superceded<javascript:taghelp('TAGS_SUPERCEDED')> by the fix in Bug:7686186<javascript:getdoc('NOTE:7686186.8');> Fixed: This issue is fixed in
* Patch 14 on Windows Platforms<javascript:getdoc('NOTE:342443.1')>
* (Server Patch Set)<javascript:taghelp('FIXED_A205')>
* (Base Release)<javascript:taghelp('FIXED_B200')>
-- Mladen Gogala Oracle DBA Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.** --
Received on Tue Jun 02 2015 - 17:26:52 CEST

Original text of this message