Today we will checkout a PL/SQL procedure that will keep monitoring Oracle EBS application specific Tablespaces and send emails to concerned parties whenever the free storage falls below a certain percentage. The below stored procedure checks the free spaces of all Tablespaces that have a name starting with “APPS”.

create or replace procedure XXTBLSPCE_MAIL( errbuf  OUT    VARCHAR2, retcode   OUT    VARCHAR2) AS
cursor c1 is
SELECT F.TABLESPACE_NAME,
(T.TOTAL_SPACE -  F.FREE_SPACE) "USED_MB",
(F.FREE_SPACE) "FREE_MB",
(T.TOTAL_SPACE) "TOTAL_MB",
(ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) PER_FREE
 FROM   (
 SELECT       TABLESPACE_NAME,
 ROUND (SUM (BLOCKS*(SELECT VALUE/1024
 FROM V$PARAMETER
 WHERE NAME = 'db_block_size')/1024)
 ) FREE_SPACE
 FROM DBA_FREE_SPACE
 WHERE TABLESPACE_NAME LIKE 'APPS%'
 GROUP BY TABLESPACE_NAME
 ) F,
 (
 SELECT TABLESPACE_NAME,
 ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME
 ) T
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
 AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
 
 /* Mail specific variables*/

mailhost VARCHAR2 (30) := 'smtp.yourmaildomain.com';
mail_conn utl_smtp.connection;
crlf VARCHAR2(2) := CHR (13) || CHR (10);
message_1 CLOB;
v_email varchar2(16000);
v_sub varchar2(10000);
vcount NUMBER := 0;

name_array     DBMS_SQL.varchar2_table;

CC_parties     VARCHAR2 (2000) := NULL;

Cursor ec1 is
(select 'rec1.it@yourmaildomain.com'  user_email from dual
union all
select 'rec2.it@yourmaildomain.com'  user_email from dual
);


 v_Mail_Exception Exception;
 
 --15/10/2020
 
   l_host_name varchar2(240);
   l_db_name varchar2(240);
 
 BEGIN
 
  BEGIN
        select sys_context ( 'USERENV', 'DB_NAME' ) db_name,
        sys_context ( 'USERENV', 'SERVER_HOST' ) db_host into l_db_name, l_host_name
        from dual;
        EXCEPTION 
        WHEN NO_DATA_FOUND THEN
        l_host_name := 'NO HOST';
        l_db_name := 'NO DBNAME';
        
        END;
        

mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (mail_conn, mailhost);
utl_smtp.mail (mail_conn, 'sender@yourmaildomain.com');

FOR m IN ec1
   LOOP
      vcount := vcount + 1;
      name_array (VCOUNT) := m.user_email;
   END LOOP;
   
   

 FOR n IN name_array.FIRST .. name_array.LAST
   LOOP
      CC_parties := CC_parties || ';' || name_array (n);
      UTL_SMTP.Rcpt (mail_conn, name_array (n));
   END LOOP;

if CC_parties is NULL then
RAISE v_Mail_Exception;
end if;

for ts in c1 loop
if (ts.FREE_MB < 1024) then
if (trim(message_1) is not null) then
message_1 := message_1||ts.tablespace_name ||' is running out of storage space. Current free space is '||ts.FREE_MB||' MB. ';
else
message_1 := ts.tablespace_name ||' is running out of storage space. Current free space is '||ts.FREE_MB||' MB. ';
end if;
end if;
end loop;

if (trim(message_1) is not null) then
message_1 := message_1||' ( Note: add datafiles with size nothing less than 2GB for APPS_TS tablespaces.) '; 

v_sub := 'Subject: '||'Table Space(s) running out of space! Urgent(Instance: '||l_host_name||', Database: '||l_db_name||' )';
 message_1 :=  'From: Oracle Database sender  < sender@yourmaildomain.com >' ||crlf||v_sub||crlf||'To:'||CC_parties||crlf||crlf||crlf||message_1;
 
 utl_smtp.data(mail_conn, 'MIME-Version: 1.0' ||CHR(13)||
 CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||message_1);
 utl_smtp.quit (mail_conn);
 
 end if;
 

EXCEPTION
WHEN v_Mail_Exception Then
   null;
WHEN OTHERS THEN

--err_code := SQLCODE;
--err_msg := SUBSTR(SQLERRM, 1, 200);
message_1 := 'Failed to send email, error explanation:  '||SQLCODE||'  '||SQLERRM;

v_sub := 'Subject: '||'Table Space(s) running out of space! Urgent';
message_1 := 'From: Oracle Database sender  < sender@yourmaildomain.com >' ||crlf||v_sub||crlf||crlf||message_1;

 utl_smtp.data(mail_conn, 'MIME-Version: 1.0' ||CHR(13)||
 CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)||message_1);
 utl_smtp.quit (mail_conn);
 

 End;

Create a concurrent program and submit as a scheduled, setting up frequencies based on your specific requirements. The above program works perfectly under the below conditions.

Oracle EBS R12 12.2.10, 19c multi-tenant database, Local SMTP server.