Optimized/enhanced Oracle Tablespace Check

by Jan 9, 2010

Hi everyone,

I havent posted in forever, but this is a good one, so I thought I would share. Those of you who use the default oracle tablespace check know that it can be a pig. Here is an optimized query, that takes fewer resources and returns very quickly. It was written by one of our DBA's. Please feel free to reply or pm me if you need assistance integrating…

Instead of using uptime's check for freespace in datafiles:

I have my own:
SELECT t.tablespace_name
, t.sz “Total Bytes”
, round(f.sz,2) “Bytes Free”
, round(f.sz/t.sz*100,2) “% Free”
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 sz
FROM dba_free_space f
GROUP BY tablespace_name) f
, (SELECT tablespace_name, SUM(bytes)/1024/1024 sz
FROM dba_data_files
GROUP BY tablespace_name) t
WHERE f.tablespace_name = t.TABLESPACE_NAME;

It returns in a second and it returns the sum of all the freespace not just the largest segment.