Donald K. Burleson Oracle Tips

A tablespace-full alert script

Here is a great script to alert the DBA whenever a tablespace grows beyond 95% full.  This script can be run with a Unix cron every tem minutes, and send a pager alert or e-mail to the DBA whenever a data file is needed for a tablespace.

Of course, many shops are using the AUTOEXTEND feature for tablespaces, in which case you should be monitoring your filesystems, and not just the Oracle tablespaces.

REM © 1996 by Donald K. Burleson

SET ECHO OFF;

SET TERM OFF;

SET TIMING OFF;

SET HEAD OFF;

SET FEED OFF;

CREATE TABLE temp_ts (tablespace_name,total_bytes,free_bytes,max_chunk)

  AS

  SELECT tablespace_name, NVL(SUM(bytes), 1), 1, 1

  FROM dba_data_files

  GROUP BY tablespace_name;

  UPDATE temp_ts a

  SET a.free_bytes = (SELECT NVL(SUM(b.bytes), 1)

                      FROM dba_free_space b

                      WHERE b.tablespace_name = a.tablespace_name);

  COMMIT;

  UPDATE temp_ts a

  SET a.max_chunk = (SELECT NVL(MAX(b.bytes), 1)

                     FROM dba_free_space b

                     WHERE b.tablespace_name = a.tablespace_name);

  COMMIT;

  REM ********************************************************************

  REM Display tablespaces more than 95% full.

********************************************************************

  SELECT

         tablespace_name                                     ||

         ' is '                                              ||

         TO_CHAR(ROUND(100-(free_bytes*100/total_bytes), 2)) ||

         '% full.' T

  FROM temp_ts

  WHERE 95 < 100-(free_bytes*100/total_bytes)

  ORDER BY tablespace_name;

Here is a sample of the output from this script, alerting you to tablespace that are almost full and tables that cannot take a NEXT extent.

NON-EMERGENCY ORACLE ALERT. Contact the DBA and report this error ===>Sun Feb 18 07:00:02 EST 2001 BOOKD is 100% full. BOOKD READER BOOK TABLE BOOKD READER PAGE TABLE BOOKD READER PAGE_IMAGE TABLE BOOKD READER SAMPLE_CONTROL TABLE BOOKD READER TOC_ENTRY TABLE

You can also easily extend this script to send you an e-mail alert when the tablespace become too full.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: