After suspend trigger to send out email alert to DBA about resumable space suspend

Environment
2-Nodes 11.2.0.2 Oracle RAC on OEL 5.5x64 bit platform.


Problem
During Datapump Import, when a tablespace runs out of space, the import job will be in "ORA-39171: Job is experiencing a resumable wait" state. By default, the database will not send out any alert to DBA about this problem unless DBA is actively monitoring this import job or check the alert log file. 


Solution
To address this problem and free DBAs from constantly monitoring the import job, a "AFTER SUSPEND System Event Trigger" will be created in the database, which will then email DBAs when a "resumable wait" problem happens in the database. 
1 Check system resumable_timeout setting
SQL> show parameters resumable_timeout


2 Set resumable_timeout value
If the above query shows resumable_timeout=0, change it to a desired seconds:
SQL> alter system set resumable_timeout=3600 scope=both;


Note: to understand the usage of this parameter, this is a good information page - Oracle Resumable Space Allocation

3 Enable UTL_MAIL package in order to use utl_mail to send out alert emails
3.1 created the UTL_MAIL package by running the following script under SYS schema

 SQL> @?/rdbms/admin/utlmail.sql

SQL> @?/rdbms/admin/prvtmail.plb

3.2 Grants the execute on UTL_MAIL privilege either to PUBLIC or to the user 
   which will use the package, running one of this statement from SYS:
SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
     -or-
SQL> GRANT EXECUTE ON UTL_MAIL TO ;

Note: 

-- How to Use the UTL_MAIL Package [ID 269375.1]

-- How to Send Any Type of Attachment with UTL_MAIL [ID 946811.1]

3.3 Make sure the SMTP_OUT_SERVER parameter is defined in the database

SQL> show parameters smtp_out_server

Configure it if it's not set:

SQL>alter system set smtp_out_server=mail.hostname.com scope=both;

Note:

--UTL_MAIL Package

3.4 Test the utl_mail.send procedure to make sure it works

CREATE OR REPLACE PROCEDURE send_email AS
--DECLARE
   CRLF        CHAR(2) := CHR(10) || CHR(13);
BEGIN
   UTL_MAIL.SEND(
        sender => 'yi.lin@wealthengine.com'
       ,recipients => 'yi.lin@wealthengine.com'
       ,cc => NULL
       ,bcc =>  NULL
       ,subject => 'Test Email'
       ,message => 'Test line1' || CRLF || CRLF
       || 'Test line2'
       ,mime_type => 'text/plain; charset=us-ascii'
       ,priority => 1
   );


   EXCEPTION
   WHEN OTHERS THEN
    --  dbms_output.put_line('Fehler');
     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
   END;
   /
SHOW ERRORS


exec send_email;


4 After successful testing of the utl_mail.send procedure, create a "AFTER SUSPEND System Event Trigger" in the database:

CREATE OR REPLACE TRIGGER resumable_alert
AFTER SUSPEND
ON DATABASE
DECLARE
CRLF        CHAR(2) := CHR(10) || CHR(13);
BEGIN
   UTL_MAIL.SEND(
        sender => 'sting@wealthengine.com'
       ,recipients => 'yi.lin@wealthengine.com'
       ,cc => NULL
       ,bcc =>  NULL
       ,subject => 'Space resumable alert found in the database'
       ,message => 'Space resumable alert found in the database' || CRLF || CRLF
       || ' Iusse '       ,mime_type => 'text/plain; charset=us-ascii'       ,priority => 1   );   EXCEPTION   WHEN OTHERS THEN    --  dbms_output.put_line('Fehler');     raise_application_error(-20001,'The following error has occured: ' || sqlerrm);   END;   /SHOW ERRORSNote: --AFTER SUSPEND Trigger--RESUMABLE SPACE ALLOCATION in Oracle 10g--A Simple Example On AFTER SUSPEND System Event Trigger [ID 258827.1]