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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mladen Gogala
Sent: Tuesday, June 02, 2015 11:13 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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.**
