Oracle 11.2.0.4 on Linux
In OEM, the tablespace page reports that my system tablespace is 100% full. However, each of the following queries report that the tablespace utilization is about 27%:
select * from dba_tablespace_usage_metrics where TABLESPACE_NAME = 'SYSTEM';
select SUM(MAXBYTES), sum(bytes), SUM(BYTES)/SUM(MAXBYTES) * 100 from dba_data_files where tablespace_name = 'SYSTEM';
select max(f.maxbytes), sum(s.bytes), sum(s.bytes)/max(f.maxbytes) * 100
from dba_data_files f join dba_segments s on f.tablespace_name = s.tablespace_name
where f.tablespace_name = 'SYSTEM'; -- system has only 1 data file.
I'm trying to determine whether I have a real problem or not. My theory is that the utilization reported in OEM is like the high water mark in that, at some point, extents were allocated to max number of extents and all of those extents were filled. But since then, data has been selected from the tablespace. However, I haven't been able to get confirmation of this hypothesis. I would appreciate any clarification.