RE: How does EM12c calculate tablespace freespace?
Date: Tue, 2 Jun 2015 15:26:52 +0000
Message-ID: <C1FB7BA65B13C542B2CB1CE5DB8F74AF19D34039_at_NC2PWEX501.us.ad.lfg.com>
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.
Thanks
Scott
From: Deas, Scott
Sent: Tuesday, June 02, 2015 11:26 AM
To: oracle-l_at_freelists.org
Subject: RE: How does EM12c calculate tablespace freespace?
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> [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<mailto: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 affectedReceived on Tue Jun 02 2015 - 17:26:52 CEST
* 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