RE: How does EM12c calculate tablespace freespace?

From: Deas, Scott <Scott.Deas_at_lfg.com>
Date: Tue, 2 Jun 2015 15:25:45 +0000
Message-ID: <C1FB7BA65B13C542B2CB1CE5DB8F74AF19D3401A_at_NC2PWEX501.us.ad.lfg.com>

We have environments through 11.2.0.4 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.

Thanks,
Scott

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Tuesday, June 02, 2015 11:13 AM
To: oracle-l_at_freelists.org
Subject: Re: How does EM12c calculate tablespace freespace?

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

Hey all,

EM 12.1.0.3 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 11.2.0.3 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

Browser.

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

with this.

Thoughts anyone?

Rich

--

http://www.freelists.org/webpage/oracle-l





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:
Bug 6759910  DBA_TABLESPACE_USAGE_METRICS USED_PERCENT may be wrong
 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.
Affects:
Product (Component)

Oracle Server (Rdbms)

Range of versions believed to be affected

Versions < 11.2

Versions confirmed as being affected




  *   10.2.0.4<javascript:taghelp('AFFECTS_A204')>


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




  *   10.2.0.4 Patch 14 on Windows Platforms<javascript:getdoc('NOTE:342443.1')>

  *   10.2.0.5 (Server Patch Set)<javascript:taghelp('FIXED_A205')>

  *   11.2.0.1 (Base Release)<javascript:taghelp('FIXED_B200')>





--

Mladen Gogala

Oracle DBA

http://mgogala.freehostia.com
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.**


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 02 2015 - 17:25:45 CEST