Space Alert

Procedure Call

Space_Alert (#DBID#, #ALERTVALUE#, #ALERTSTRING#)

Description

This alert provides notification when the maximum chunk of free space within a tablespace is smaller than 2 * next_extent size of any object contained in it.

PL/SQL Code For Blocking_Lock_Alert Procedure

CREATE OR REPLACE PROCEDURE Space_Alert (

       pDBID         in number,

       pAlertValue   out varchar2,

        pAlertString out varchar2)

AS

   TYPE MyCursorType IS REF CURSOR;

   MyCursor MyCursorType;

   sDBName           VARCHAR2(50);

   sSQL              VARCHAR2(2000) := '';

   sEmailText        VARCHAR2(8000) := '';

   nNextExtent             NUMBER;

   sTSName           VARCHAR2(30);

   nTSMaxBytes             NUMBER;

   nRows             NUMBER := 0;

   sCRLF             VARCHAR2(2) := chr(13)||chr(10);

BEGIN

   -- get the database name

   SELECT name INTO sDBName FROM cond WHERE id = pDBID;

   -- setup SQL statement that will find the problems

   sSQL := 'select tablespace_name, max(next_extent), max(max_bytes) from '

        || '   (select free_space.tablespace_name, s.next_extent, free_space.max_bytes '

        || '   from dba_segments@'||sDBName||' s, ( '

        || '      select   t.tablespace_name, nvl(max(f.bytes),0) max_bytes '

        || '      from     dba_tablespaces@'||sDBName||' t, dba_free_space@'||sDBName||' f '

        || '      where    t.tablespace_name = f.tablespace_name '

        || '      and      t.tablespace_name not in (''TEMP'',''UNDOTBS'',''TOOLS'') '

        || '      group by t.tablespace_name) free_space '

        || '   where s.tablespace_name = free_space.tablespace_name '

        || '   and   s.next_extent*2 > free_space.max_bytes '

        || '   order by tablespace_name) '

        || 'group by tablespace_name';

   -- loop through the results and build an email

   OPEN MyCursor FOR sSQL;

   LOOP

      FETCH MyCursor INTO sTSName, nNextExtent, nTSMaxBytes;

      EXIT WHEN MyCursor%NOTFOUND;

      sEmailText := sEmailText || 'Tablespace ' || sTSName || ' in database ' || sDBName || ' would not be able to '

          || 'extend by ' || nNextExtent*2 || ' bytes. Current free space is ' || nTSMaxBytes || '.' || sCRLF;

      nRows := nRows + 1;

   END LOOP;

   IF nRows > 0 THEN

      pAlertString := sEmailText;

      pAlertValue := 'TRUE';

   ELSE

      pAlertString := '';

      pAlertValue := 'FALSE';

   END IF;

END Space_Alert;

/

For more information about Writing Custom Alerts, see PG110.