Monitor tablespace free space and get email alerts
                                                 Last update (2009-10-26 21:00:16)
                                                                                                                   Date added (2008-02-19 10:52:06)

Summary
One of the daily checks is if you have enough free space at the tablespaces.

You can use Oracle Enterprise Manager until Oracle9i, or DB console, or Grid Control from Oracle10g and afterwards to get email notifications about everything. But I don't like it, and especially when a database has thousands of sessions. For example for more than 3000 sessions, the Grid Control takes appoximately a 10% of the machine's processing power, so i prefer my own alert mechanisms.

Explanation
You need to setup.
1. One procedure(Check_Tablespaces) that checks for free available space and sends email alerts.
2. One job that checks at a period of time executing the procedure
3. One procedure that sends emails and recipient

1. Create the procedure
CREATE OR REPLACE PROCEDURE Check_Tablespaces (VAR_PER_FREE_SPACE NUMBER)
IS

   CURSOR CUR_RECIPIENTS IS SELECT * FROM HELPDESK.S_RECIPIENTS;

   CURSOR TABLESPACE_CURSOR
   IS
      SELECT NVL(b.tablespace_name,
             NVL(a.tablespace_name,'UNKOWN')) tablespace_name,
       ROUND(Mbytes_alloc) MB_Alloc,
	   ROUND(NVL(Mbytes_free,0)) MB_Free,
       ROUND(Mbytes_alloc-NVL(Mbytes_free,0)) MB_Used,
	   ROUND(((NVL(Mbytes_free,0))/Mbytes_alloc)*100) pct_free,
       ROUND(((Mbytes_alloc-NVL(Mbytes_free,0))/Mbytes_alloc)*100) pct_used,
       DECODE(Mbytes_max, 0, 'NO', 'AUTO') EXTENSIBLE
FROM ( SELECT SUM(bytes)/(1024*1024) Mbytes_free,
              MAX(bytes)/(1024*1024) largest,
              tablespace_name
       FROM  sys.dba_free_space
       GROUP BY tablespace_name ) a,
     ( SELECT SUM(bytes)/(1024*1024) Mbytes_alloc,
              SUM(maxbytes)/(1024*1024) Mbytes_max,
              tablespace_name
       FROM sys.dba_data_files
       GROUP BY tablespace_name) b
WHERE a.tablespace_name (+) = b.tablespace_name AND Mbytes_max = 0
ORDER BY pct_free ASC;

V_DATABASE V$PARAMETER.VALUE%TYPE;
crlf VARCHAR2(2):= CHR( 13 ) || CHR( 10 );

BEGIN
SELECT VALUE INTO V_DATABASE FROM v$parameter WHERE NAME = 'db_name';

   FOR c1 IN TABLESPACE_CURSOR
   LOOP
   IF c1.PCT_FREE <= VAR_PER_FREE_SPACE THEN
   FOR c2 IN cur_recipients LOOP
		   BEGIN
            Send_Mail('skaparelis@ote.gr', c2.recipient,
                      'Subject: ' || 'DB Server:  ' || V_DATABASE || '  ' || 'Tablespace: ' || c1.TABLESPACE_NAME || ' (' || c1.pct_free || '%) free space' || crlf,
                      'DB Server:  ' || V_DATABASE || '  ' || crlf ||
                      'Tablespace: ' || c1.TABLESPACE_NAME || crlf ||
                      'MB Allocated:' || c1.MB_ALLOC || crlf ||
                      'MB Free:' || c1.MB_FREE || crlf ||
                      'MB Used:' || c1.MB_USED || crlf ||
                      'PCT Free:' || c1.PCT_FREE ||'%' || crlf ||
                      'PCT Used:' || c1.PCT_USED ||'%' || crlf);
		    END;
		END LOOP;
      END IF;
      END LOOP;

EXCEPTION
   WHEN OTHERS
   THEN
NULL;
END Check_Tablespaces;
/
2. Create the job, for example the following job checks every 30 minutes and sends email alerts if a tablespace has less or equal to 2% free space.
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => 'SYS.Check_Tablespaces
  (2);'
     ,next_date => TO_DATE('20/02/2008 00:00:00','dd/mm/yyyy hh24:mi:ss')
     ,INTERVAL  => 'SYSDATE+30/1440 '
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || TO_CHAR(x));
END;
/

COMMIT;
Reviews
Filters
Search