How does EM12c calculate tablespace freespace?

From: Rich Jesse <rjoralist3_at_society.servebeer.com>
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-l
Received on Tue Jun 02 2015 - 16:04:07 CEST

Original text of this message