How does EM12c calculate tablespace freespace?
Date: Tue, 2 Jun 2015 09:04:07 -0500 (CDT)
Message-ID: <ec9eb322482c95e9f4b7926159e44f6b.squirrel_at_society.servebeer.com>
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-lReceived on Tue Jun 02 2015 - 16:04:07 CEST