Script to show problem tablespaces

Script to show problem tablespaces

[Ed. note: This script is now corrected and has been tested on 8.1.7.4 and 9.2.0.6.0.] I've seen a lot of scripts that tell you about all the tablespaces in a database, but very few show only the ones that are going to give you problems. I've been using this script for a few years now and it has really saved me from dialing in at nights and on the weekends. I use it as a cursor for a procedure and have it build an e-mail and/or page notification that is sent to myself and others. This script is useful because it drills down to what is going to give you a problem. I don't have a lot of time to wade through a reports to find out which tablespace is running out of space, this is short and sweet and lets me get on with my day. I've run the script on 8, 8i, and 9i. Just make sure you are using system or another user that can read the data dictionary.   SELECT space.tablespace_name, space.total_space, free.total_free,    ROUND(free.total_free/space.total_space*100) as pct_free,    ROUND((space.total_space-free.total_free),2) as total_used,    ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,    free.max_free, next.max_next_extent FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 total_space FROM dba_data_files GROUP BY tablespace_name) space, (SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free FROM dba_free_space GROUP BY tablespace_name) free, (SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments GROUP BY tablespace_name) NEXT WHERE space.tablespace_name = free.tablespace_name (+) AND space.tablespace_name = next.tablespace_name (+) AND (ROUND(free.total_free/space.total_space*100) /*pct_free*/ < 10 OR next.max_next_extent > free.max_free) /

REFERENCES

Script to show problem tablespaces by Brett Ogletree