Problem solve Get help with specific problems with your technologies, process and projects.

Script to show problem tablespaces

This handy little script will quickly show you which tablespaces are running out of space.

[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) /

 

Reader feedback

Perry W. writes: "This script is based on an Oracle7 mentality. It does not provide valid data if autoextend is used for datafiles. The column max_bytes must be used to identify how large a datafile can *potentially* grow. He must also account for the fact some datafiles may have autoextend on and some may not. Also, a monitor for disk space available must also be included in the monitoring infrastructure. This is a rookie script in my opinion and can provide misleading results with autoextend turned on.

Tom T. writes: "Nice and simple, but you might want to include the new views DBA_TEMP_FILES, etc. Might make for a more complete report."

 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.


This was last published in August 2005

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close