The Oracle DBAs at my org have requested a new query to use to track tablespace utilization. It has significantly different metrics for us, but they appear a lot more useful. This matches the numbers that are reported to them in the Oracle Enterprise Manager. The dba_tablespace_usage_metrics table should be available in Oracle 10g and up.

SELECT dt.tablespace_name as tablespace, dt.contents as type, dt.block_size * dtum.used_space as bytes, dt.block_size * dtum.tablespace_size as max_bytes, dt.block_size * (dtum.tablespace_size - dtum.used_space) as free FROM dba_tablespace_usage_metrics dtum, dba_tablespaces dt WHERE dtum.tablespace_name = dt.tablespace_name ORDER by tablespace

Here's what's different for us:

• The existing query will show both an undotbs1 and undotbs2 tablespace, with the second one at 0%. I'm told that second one will begin to show up in dba_tablespace_usage_metrics as soon as it's used, like when activating a second node or assigning it as an active undo_tablespace.
• The numbers are way different, but hopefully way more useful. The existing query is aggregating the size and max_size of all the files in each tablespace. This method of capturing them is measuring by the number of extents used by those file objects, but that is going to be much higher than the actual space utilization. Even when a file gets smaller, those extents are available for it to grow into without consuming more space.
• The query above is actually reporting on blocks (hence the conversion to bytes) that are actually in use. It won't count unused extents that haven't been deallocated from a given file, even though they will be either deallocated or re-used if needed.

I'm running both the old and new queries in parallel in our environments now for a week to compare, and I expect I'll switch over to the new one entirely. I can provide a better glimpse of how different it is then, but I suspect it won't be close (especially for tmp/undo/system tablespaces). I'd be happy to provide this as a PR to update the query used in default-metrics.conf, but I was hesitant to do it without knowing how to handle such a "breaking" change, where a lot of user thresholds based on the old calculation may be inappropriate on the new calculation.