Re: How does EM12c calculate tablespace freespace?
- From: "Mladen Gogala" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "mgogala@xxxxxxxxx" for DMARC)
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 02 Jun 2015 11:12:56 -0400
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
Other related posts:
