Re: How does EM12c calculate tablespace freespace?
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Tue, 02 Jun 2015 11:12:56 -0400
Message-ID: <556DC7F8.8010407_at_yahoo.com>
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
>
>
Date: Tue, 02 Jun 2015 11:12:56 -0400
Message-ID: <556DC7F8.8010407_at_yahoo.com>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 02 2015 - 17:12:56 CEST